Real Time DSS: servers

Showing posts with label servers. Show all posts

Friday, March 30, 2012

Recipe for a good, solid maintenance plan

Hello

I'm in the proces of a major revision of the maintenance plans for the SQL servers in our company, and in connection with that I would like to hear how other people are doing this.

Here's a quick rundown of the plan:

Critical DBs will be backed up on tape (daily incremental, weekly full, w. Veritas Backup Exec 9.0). Also, there will be full daily disk backups for easy quick recovery. These will be done locally, as I have bad experiences trying to backup across a network share.

DBs of medium importance will be backed up fully every day on disk. These BAK-files will then be backed up on tape, if I find it necessary.

Although I rarely restore from the tapes, I think they're nice to have in case the office burns down or who knows what.

The maintenance plans will be split into 3:
1. System DBs maint. plan
2. Critical importance DBs maint. plan
3. Medium importance DBs maint. plan

The more I think about it, the more I think I might just classify all production DBs as critical and all test DBs as medium. Maybe that would make more sense.

For all disk backups, optimization and integrity checks (and backup) will be done daily. For DBs of critical importance (eg. production DBs) Transaction log back will be done as well. What's a good schedule for this? Once every 3 hours or so? Every hour? How much burden does this operation put on the server?

I guess that's about it so far. If anyone has any suggestions or comments, I would be very pleased to hear them.

MNJFrequency of trx. log backups depends on the level of activity of action queries and recoverability requirements. In one of our databases here we're doing 15-minute trx. log dumps and the resulting file varies from 800MB to 2.5GB in size. Another database barely creates a 100K logs but we're doing dumps every 30 minutes for its point-in-time recoverability requirements. It all depends.

As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south :)|||[i]As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south :)

Why is it beneficial to backup the system DBs last? Since I keep system and user DBs separated into different maint. plans, I guess I can just schedule the system maint. plan to occur 15 min. after the user main. plan?

MNJ|||For one, if MSDB is backed up last, - it will contain the latest backup information of all other databases, as well as itself. This information is available when looking at the General tab of database Properties window.

As per scheduling, - as I said earlier, I have execution of all maintenance plans in one batch with SQLMAINT. If you're using Scheduled Tasks, then you can add a step with SQLMAINT -PlanName <sys_db_maint_plan> after your application databases.|||Good point with system DBs, I will take that into consideration. I suppose if I just make sure to schedule them a bit apart, it should work out ok.

MNJ

Receiving queue does not fire stored procedure

I am doing my first SSB application where two different servers send messages to each other. The logic was all previously tested on a single server between two databases and it worked OK.

The problem I am having is that when the message is received at the target server (I see this in profiler), the stored procedure associated with the queue does not fire.

I see an acknowledgment fire back to the initiator, but it is like the target server does nothing with the initial message.

Any ideas on how I can further troubleshoot this? FWIW, I used the setup tool provided by RemusResanu to set up the routes and service bindings.

Thanks for any help!
John
Here is what my target server shows in profiler

Event Class/Sub Class

Broker:Conversation Group 1 - Create
Broker:Conversation 12 - Dialog Created
Broker:Conversation 6 - Received Sequenced Message
Broker:Remote Message Acknowledgement 3 - Message with Acknowledgement Received
Broker:Message Classify 2 - Remote
|||Also, when I query the queue, I can see all of the messages just sitting there. They are reaching the right place, just nothing happens from there.

Please help!
John

Code Snippet

CREATE QUEUE [dbo].[SiteChangeNotifyReceiveQueue] WITH STATUS = ON , RETENTION = ON , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[SiteChangeQueueReader] , MAX_QUEUE_READERS = 10 , EXECUTE AS N'sqlservices' ) ON [PRIMARY]

|||Have you tried to execute the procedure manually?
Can the user 'sqlservices' be impersonated? I.e. executing EXECUTE AS USER='sqlservices' works fine w/o errors?
Do you see any errors in the ERRORLOG file or in the NT application event log (eventvwr.exe)?|||Yes, I just tried that and it worked fine. In fact, it seemed to go in to the queue and process all of the pending messages.

So, here is the really strange part:

Now, everything works fine.

After I ran the SP manually, my messages come through and the SP fires by itself again.

I would really like it if someone could give me an explanation of this. Is this part of Service Broker enforcing the order of transactions- there were errors in my SP at one time. Perhaps the SSB was not processing the later messages until the old ones went through?

I don't t like it when I can't explain how things went from broken to working!
|||If the situation repeats please look at sys.dm_broker_queue_monitors and tell us what is the state of the queue with the problem.

Wednesday, March 28, 2012

Receive alerts on SQLServer service up/down status

I am researching about this capability within SQL 7.0. I would like to use 1 SQL server to monitor all other SQL servers on "SQL Server service up/down" condition. I don't seem to be able to do it because when SQLServer service is down, the SQLAgent service stopped as well.
And, I think SQLAgent service is needed to communicate the status.
Does anyone know if this capability exist in SQL 7 or not? If not, I think I will look into other tools. Any information is appreciated. Thanks in advance!Debbie,
You could create a periodic SQL agent job on the monitoring server that polls
the others by attempting to run a simple stored proc or similar.
The on failure step can be set to do the alarm.
I'm not sure what you mean by the service up/down problem. Are you talking
about the monitoring sql server ?
The SQL Agent has SQL Server as a dependancy, so it wont run without it.
But they are real separate services, so you could "monitor" them by service
control manager commands.
Regards
AJ
"Debbie" <anonymous@.discussions.microsoft.com> wrote in message
news:BC78F429-88C2-4961-8010-969059F95D91@.microsoft.com...
> I am researching about this capability within SQL 7.0. I would like to use 1 SQL server to monitor all
other SQL servers on "SQL Server service up/down" condition. I don't seem to be able to do it because when
SQLServer service is down, the SQLAgent service stopped as well.
> And, I think SQLAgent service is needed to communicate the status.
> Does anyone know if this capability exist in SQL 7 or not? If not, I think I will look into other tools.
Any information is appreciated. Thanks in advance!|||You'd be far better off using a script written in a language other than
T-SQL for this type of monitoring purposes. The main reason is that many
error messages are sent to the client and the client needs to be able to
trap and check these error messages and respond properly. A real scripting
language is also better in handling whatever notification logic you want to
implement and in sending alerts
You can write a simple script to periodically try to connect to each of the
SQL instances you want to monitor. If the connection attempt fails, the
script can then decide whether to send an alert. You may want send an alert
only when there are several consecutive failed connection attempts in order
to minimize nuisance alerts.
If you are not into scripting, use a third-party package. But these
commerical tools often come with a lot of excessive weight, offering far
more than you need and costing a bundle.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Debbie" <anonymous@.discussions.microsoft.com> wrote in message
news:BC78F429-88C2-4961-8010-969059F95D91@.microsoft.com...
> I am researching about this capability within SQL 7.0. I would like to
use 1 SQL server to monitor all other SQL servers on "SQL Server service
up/down" condition. I don't seem to be able to do it because when SQLServer
service is down, the SQLAgent service stopped as well.
> And, I think SQLAgent service is needed to communicate the status.
> Does anyone know if this capability exist in SQL 7 or not? If not, I
think I will look into other tools. Any information is appreciated. Thanks
in advance!

Monday, March 26, 2012

recbase.cpp:1378 & somerge.c:1616 Errors

Hi,
I am trying to implement a DTS to copy data from one server to another
(both servers are identical), Windows 2000 server and have installed
SQL Server 2000 sp3a.
The DTS in question has the two server connections defined as
"Microsoft OLE DB Provider for SQL Server" and there are several
"Transform Data Tasks" that copy data from tables on one server to
tables on the other. Unfortunately I have to run these steps often as
I am in the process of developing/debugging and I am experiencing one
of the following errors, always when the DTS arrives at the task of
copying the same large table:
Location somerge.c:1616
Expression: mrP->mrStatus!=MERGERUN::NONE
SPID: 78
Process ID: 724
Location recbase.cpp:1378
Expression: m_offBeginVar < m_sizeRec
SPID: 64
Process ID: 840
These errors do not always occur but either one or the other pops up
on a regular basis.
I have run DBCC checkDB and DBCC checktable on the affected Databases
and tables several times and have never found any errors.
Has anyone got any idea of what is causing this? Any suggestions of
what I can do to solve this?
Any help is much appreciated.
Regards
Rekkie
Dear ,
While executing this particular process both servers "sa" passwords should be same,Other wise it never work.
Regards
Faheem Latif
Netsol Pvt Ltd

recbase.cpp:1378 & somerge.c:1616 Errors

Hi,
I am trying to implement a DTS to copy data from one server to another
(both servers are identical), Windows 2000 server and have installed
SQL Server 2000 sp3a.
The DTS in question has the two server connections defined as
"Microsoft OLE DB Provider for SQL Server" and there are several
"Transform Data Tasks" that copy data from tables on one server to
tables on the other. Unfortunately I have to run these steps often as
I am in the process of developing/debugging and I am experiencing one
of the following errors, always when the DTS arrives at the task of
copying the same large table:
Location somerge.c:1616
Expression: mrP->mrStatus!=MERGERUN::NONE
SPID: 78
Process ID: 724
Location recbase.cpp:1378
Expression: m_offBeginVar < m_sizeRec
SPID: 64
Process ID: 840
These errors do not always occur but either one or the other pops up
on a regular basis.
I have run DBCC checkDB and DBCC checktable on the affected Databases
and tables several times and have never found any errors.
Has anyone got any idea of what is causing this' Any suggestions of
what I can do to solve this?
Any help is much appreciated.
Regards
RekkieDear ,
While executing this particular process both servers "sa" passwords should b
e same,Other wise it never work.
Regards
Faheem Latif
Netsol Pvt Ltd|||Both passwords are the same. I don't see however what has
to do with the problem as each OLEDB server connection is
defined separately with the user to use for that
connection.

>--Original Message--
>Dear ,
>While executing this particular process both servers "sa"
passwords should be same,Other wise it never work.
>Regards
>Faheem Latif
>Netsol Pvt Ltd
>.
>

Wednesday, March 21, 2012

Rebuild one node from SQL Cluster

hi guys,
I have SQL Cluster running on 2 servers running on Win2k3. The SQL Cluster
is on Active/Passive config. One of the node giving problem and i am planning
to rebuild it until this question strikes my mind. After rebuild, how do i
join back to existing SQL Cluster?
TIA
oh...it's k...i got it
http://www.microsoft.com/technet/pro...rt4/c1261.mspx
"Adding or Removing a Cluster Node from the Virtual Server Definition
Another new feature of SQL Server 2000 failover clustering is the ability to
add or remove a cluster node from a SQL Server virtual server definition.
Adding nodes to the existing SQL Server virtual server definition performs
all the necessary operations on the new nodes (including installing binaries,
system components, and creating services) and performs the necessary
modifications to the cluster configuration. "
"rupart" wrote:

> hi guys,
> I have SQL Cluster running on 2 servers running on Win2k3. The SQL Cluster
> is on Active/Passive config. One of the node giving problem and i am planning
> to rebuild it until this question strikes my mind. After rebuild, how do i
> join back to existing SQL Cluster?
> TIA
|||You'll get the right audience looking at this if you plst to
ms.p.sqlserver.clustering.
There are these links
http://www.microsoft.com/technet/pro.../failclus.mspx
http://www.microsoft.com/downloads/d...displaylang=en
http://www.sql-server-performance.co...stall_main.asp
http://support.microsoft.com/?kbid=301600
Which should help but some questions first.
What SP level is the OS and SQL currently at?
There are issues adding back failed nodes to SP4 level (I had them).
Also you must follow certain guidelines when evicting/adding nodes otherwise
you could render your cluster unusable.
Also have a backup plan in case this actually does happen. I restored the
critical databases to another cluster just prior to doing the actual work on
a live system just in case it all went wrong.
Also if you have access to a "virtual server system" Virtual Server 2005 or
VMWare create a system similar to you current one to try the process out.
Nik Marshall-Blank MCSD/MCDBA
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:7BE08633-F659-4B34-9F5E-806A3550CA02@.microsoft.com...
> hi guys,
> I have SQL Cluster running on 2 servers running on Win2k3. The SQL Cluster
> is on Active/Passive config. One of the node giving problem and i am
> planning
> to rebuild it until this question strikes my mind. After rebuild, how do i
> join back to existing SQL Cluster?
> TIA

Rebuild one node from SQL Cluster

hi guys,
I have SQL Cluster running on 2 servers running on Win2k3. The SQL Cluster
is on Active/Passive config. One of the node giving problem and i am planning
to rebuild it until this question strikes my mind. After rebuild, how do i
join back to existing SQL Cluster?
TIAoh...it's k...i got it
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1261.mspx
"Adding or Removing a Cluster Node from the Virtual Server Definition
Another new feature of SQL Server 2000 failover clustering is the ability to
add or remove a cluster node from a SQL Server virtual server definition.
Adding nodes to the existing SQL Server virtual server definition performs
all the necessary operations on the new nodes (including installing binaries,
system components, and creating services) and performs the necessary
modifications to the cluster configuration. "
"rupart" wrote:
> hi guys,
> I have SQL Cluster running on 2 servers running on Win2k3. The SQL Cluster
> is on Active/Passive config. One of the node giving problem and i am planning
> to rebuild it until this question strikes my mind. After rebuild, how do i
> join back to existing SQL Cluster?
> TIA|||You'll get the right audience looking at this if you plst to
ms.p.sqlserver.clustering.
There are these links
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx
http://www.microsoft.com/downloads/details.aspx?FamilyID=96f76ed7-9634-4300-9159-89638f4b4ef7&displaylang=en
http://www.sql-server-performance.com/sqlserver2000_clustering_install_main.asp
http://support.microsoft.com/?kbid=301600
Which should help but some questions first.
What SP level is the OS and SQL currently at?
There are issues adding back failed nodes to SP4 level (I had them).
Also you must follow certain guidelines when evicting/adding nodes otherwise
you could render your cluster unusable.
Also have a backup plan in case this actually does happen. I restored the
critical databases to another cluster just prior to doing the actual work on
a live system just in case it all went wrong.
Also if you have access to a "virtual server system" Virtual Server 2005 or
VMWare create a system similar to you current one to try the process out.
--
Nik Marshall-Blank MCSD/MCDBA
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:7BE08633-F659-4B34-9F5E-806A3550CA02@.microsoft.com...
> hi guys,
> I have SQL Cluster running on 2 servers running on Win2k3. The SQL Cluster
> is on Active/Passive config. One of the node giving problem and i am
> planning
> to rebuild it until this question strikes my mind. After rebuild, how do i
> join back to existing SQL Cluster?
> TIAsql

Rebuild one node from SQL Cluster

hi guys,
I have SQL Cluster running on 2 servers running on Win2k3. The SQL Cluster
is on Active/Passive config. One of the node giving problem and i am plannin
g
to rebuild it until this question strikes my mind. After rebuild, how do i
join back to existing SQL Cluster?
TIAoh...it's k...i got it
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1261.mspx[/url
]
"Adding or Removing a Cluster Node from the Virtual Server Definition
Another new feature of SQL Server 2000 failover clustering is the ability to
add or remove a cluster node from a SQL Server virtual server definition.
Adding nodes to the existing SQL Server virtual server definition performs
all the necessary operations on the new nodes (including installing binaries
,
system components, and creating services) and performs the necessary
modifications to the cluster configuration. "
"rupart" wrote:

> hi guys,
> I have SQL Cluster running on 2 servers running on Win2k3. The SQL Cluster
> is on Active/Passive config. One of the node giving problem and i am plann
ing
> to rebuild it until this question strikes my mind. After rebuild, how do i
> join back to existing SQL Cluster?
> TIA|||You'll get the right audience looking at this if you plst to
ms.p.sqlserver.clustering.
There are these links
http://www.microsoft.com/technet/pr...n/failclus.mspx
http://www.microsoft.com/downloads/...&displaylang=en
http://www.sql-server-performance.c...om/?kbid=301600
Which should help but some questions first.
What SP level is the OS and SQL currently at?
There are issues adding back failed nodes to SP4 level (I had them).
Also you must follow certain guidelines when evicting/adding nodes otherwise
you could render your cluster unusable.
Also have a backup plan in case this actually does happen. I restored the
critical databases to another cluster just prior to doing the actual work on
a live system just in case it all went wrong.
Also if you have access to a "virtual server system" Virtual Server 2005 or
VMWare create a system similar to you current one to try the process out.
Nik Marshall-Blank MCSD/MCDBA
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:7BE08633-F659-4B34-9F5E-806A3550CA02@.microsoft.com...
> hi guys,
> I have SQL Cluster running on 2 servers running on Win2k3. The SQL Cluster
> is on Active/Passive config. One of the node giving problem and i am
> planning
> to rebuild it until this question strikes my mind. After rebuild, how do i
> join back to existing SQL Cluster?
> TIA

Rebuild master.dbo.sysperfinfo

quote:

>--Original Message--
>The system table master..sysperfinfo is a virtual one

which is dynamically

quote:

>maintained by SQL Server. So you cannot manually update

them as you wish.

quote:

>Also, it is possible that the SQL Server is started with

a -x switch which

quote:

>disables certain internal statistics. There are many

reasons why you do not

quote:

>see any performance monitor counters, some of which are

detailed below:

quote:

>http://support.microsoft.com/default.aspx?scid=kb;en-

us;246328

quote:

>http://support.microsoft.com/default.aspx?scid=kb;EN-

US;127207

quote:

>http://support.microsoft.com/default.aspx?scid=kb;EN-

US;227662 (old)

quote:

>--
>- Anith
>( Please reply to newsgroups only )
>
>.
>

sql

Rebuild master.dbo.sysperfinfo

On further study I've found that the sysperfinfo table on
the servers with the missing Performance Condition alert
type is EMPTY. I know that on one server with this
condition I upgraded from Standard Edition to Enterprise
Edition, but don't know what caused the server to lose the
data in this table.
Can anyone tell me how to rebuild the data in this table
safely. I know I can't just copy it from another server.
There's nothing in the BOL on rebuilding it.
Any ideas?
Allen White
Sr. Database Administrator
Advanstar CommunicationsThe system table master..sysperfinfo is a virtual one which is dynamically
maintained by SQL Server. So you cannot manually update them as you wish.
Also, it is possible that the SQL Server is started with a -x switch which
disables certain internal statistics. There are many reasons why you do not
see any performance monitor counters, some of which are detailed below:
http://support.microsoft.com/default.aspx?scid=kb;en-us;246328
http://support.microsoft.com/default.aspx?scid=kb;EN-US;127207
http://support.microsoft.com/default.aspx?scid=kb;EN-US;227662 (old)
--
- Anith
( Please reply to newsgroups only )|||Thanks, Anith,
While these didn't directly apply, I've found that I may
be able to solve the problem using the following statement
run from Command Prompt:
lotctr.exe c:\<SQL Server Install>\Binn\sqlctr.ini
It does require a reboot of the server, and I won't be
able to do that until late tonight, so I'll know tonight
if that solves my problem. I do know that none of the SQL
performance counters are available in Performance Monitor
either.
Allen
>--Original Message--
>The system table master..sysperfinfo is a virtual one
which is dynamically
>maintained by SQL Server. So you cannot manually update
them as you wish.
>Also, it is possible that the SQL Server is started with
a -x switch which
>disables certain internal statistics. There are many
reasons why you do not
>see any performance monitor counters, some of which are
detailed below:
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;246328
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;127207
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;227662 (old)
>--
>- Anith
>( Please reply to newsgroups only )
>
>.
>

Monday, March 12, 2012

Rebooting Mirrored Servers

New to SQL. Can you reboot the principal server after running updates without causing a failover?

If not what would the correct procedure be.

Do you have a witness?

If so, you could stop SQL Server on the mirror while you reboot the principal, that will prevent failover.

When I do this I let failover happen - it's a good test of it and it's pretty quick, once the principal is back up you can fail back over to it.

|||If there is no witness , Database Mirroring will get into disconnected state and resumes when able to communicate with Mirror.

Rebooting Cluster Boxes

I am a 2 node active/passive 2003 cluster. I have some maintenace where I need to shut down both of the servers at the same time.

Has anyone experience any issue in this approach?

I am planning to take the cluster offline and then shutdown the servers and bring it online after the reboot. I am also running SQL so any concerns or tips as what would be the best scenario.

Hoping to get some reply.

Thanks
Anup

See this KBA http://download.microsoft.com/download/1/B/D/1BDF5B78-584E-4DE0-B36F-C44E06B0D2A3/ReadmeSql2k32sp4.htm#_installing_on_a_failover_cluster readme about how you can handle such on the cluster.

But make sure you have complete backups for system and user databases to ensure to recover when something is wrong.

Wednesday, March 7, 2012

really simple question

Hi Everyone,
I'd like to test developing in SQL on my peer network before installing
the application on my clients' servers. I am using VB.Net and MSDE as
my tools.
Question:
- Does MSDE have to be installed on each station in my peer-to-peer LAN?
or
Do I _only_ need to install it on the station with the actual database
files (mdf/ldf files)?
Thanks
Richard
hi Richard,
"Richard Fagen" <no_spam@.my_isp.com> ha scritto nel messaggio
news:%23G2LfjzEEHA.3784@.TK2MSFTNGP10.phx.gbl...
> Hi Everyone,
> I'd like to test developing in SQL on my peer network before installing
> the application on my clients' servers. I am using VB.Net and MSDE as
> my tools.
> Question:
> - Does MSDE have to be installed on each station in my peer-to-peer LAN?
> or
> Do I _only_ need to install it on the station with the actual database
> files (mdf/ldf files)?
>
as MSDE acts in a traditional client/server and multi-user situation, you
usually only have to install 1 instance of MSDE on the server that all
clients want to connect to.
if every pc on the lan hosts MSDE, than usually each of them will query the
local MSDE acting as a single user or desktop solution..
in the first scenario, only 1 MSDE server is available, every client will
connect to it and both queryes and results will be sent over the network..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.6.0 - DbaMgr ver 0.52.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

really isolated

Hi
i read microsoft.com
that "web servers do not require netbios or smb" (sic)
then MS recomends to disable smb by this way:
1) "Clear the Client Microsoft Networks box"
2) "Clear the File and Printer Sharing for Microsoft Networks box"
by this way, web server stays really isolated from the intranet
is this a good practice?
because, by this way
i cant move development files to webserver
and cant do replication
thanks
(this is the link
http://msdn.microsoft.com/library/d...
d89.asp)
atte,
Hernn Castelo
UTN Buenos Aires
. . . . . . . . . . . . . . . . . . . . . . . . .
.The article dicusses;
"Disable all unnecessary protocols, including NetBIOS and SMB. Web servers
do not require NetBIOS or SMB on their Internet-facing network interface
cards (NICs). Disable these protocols to counter the threat of host
enumeration."
So, this is assuming that the web server has multiple nics installed, and
that one is internet facing. I think what's more important is to limit
the number of ports open on the box to 80 and/or 443.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||hmm...i see
ok thanks for the reply
atte,
Hernn Castelo
UTN Buenos Aires
. . . . . . . . . . . . . . . . . . . . . . . . .
.
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> escribi en el mensaje news:rHLgU
nZHEHA.660@.cpmsftngxa06.phx.gbl...
> The article dicusses;
> "Disable all unnecessary protocols, including NetBIOS and SMB. Web servers
> do not require NetBIOS or SMB on their Internet-facing network interface
> cards (NICs). Disable these protocols to counter the threat of host
> enumeration."
> So, this is assuming that the web server has multiple nics installed, and
> that one is internet facing. I think what's more important is to limit
> the number of ports open on the box to 80 and/or 443.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

Saturday, February 25, 2012

Real time replication over geographically divers sites

Hi,
Myself and a colleague have been charged with making two SQL servers
(SQL Server 2000 STD) replicate in real time, this is to allow a
helpdesk system to be updated in either the UK or South Africa.
Unfortunately as the helpdesk system we use is not by our own design
we have to use the same reference range.
Our fear is that a user would enter data in the UK taking the next
available reference at the same time as one in SA which would result
in clashing data!
Considering that the latency between these sites can be as high as
500ms what do you think would be our best solution?
Many thanks for any advice offered.
John...
What happens if the internet goes down?
You could run Merge replication every 5-10 minutes with no chance of data
clashes for tables that use identities.
Jim.
"John Norton" wrote:

> Hi,
> Myself and a colleague have been charged with making two SQL servers
> (SQL Server 2000 STD) replicate in real time, this is to allow a
> helpdesk system to be updated in either the UK or South Africa.
> Unfortunately as the helpdesk system we use is not by our own design
> we have to use the same reference range.
> Our fear is that a user would enter data in the UK taking the next
> available reference at the same time as one in SA which would result
> in clashing data!
> Considering that the latency between these sites can be as high as
> 500ms what do you think would be our best solution?
> Many thanks for any advice offered.
> John...
>
|||That depends on what the cost of latency is.
For something like this you should probably look at a citrix solution where
the UK and SA users both logon to the same database at the same time.
However, you can probably do this using replication if you relax the latency
requirements to a minute or more, and then use bi-directional transactional
replication, if and only if you can partition your data - possibly by using
dbcc checkident to create different ranges in the UK and SA.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Norton" <JNorton@.DPS-integro.co.uk> wrote in message
news:a2f128be.0503310645.16813d3d@.posting.google.c om...
> Hi,
> Myself and a colleague have been charged with making two SQL servers
> (SQL Server 2000 STD) replicate in real time, this is to allow a
> helpdesk system to be updated in either the UK or South Africa.
> Unfortunately as the helpdesk system we use is not by our own design
> we have to use the same reference range.
> Our fear is that a user would enter data in the UK taking the next
> available reference at the same time as one in SA which would result
> in clashing data!
> Considering that the latency between these sites can be as high as
> 500ms what do you think would be our best solution?
> Many thanks for any advice offered.
> John...

Monday, February 20, 2012

Reads/Duration Higher on ADO vs. QA

Hello.
I have an odd scenario. I just swapped out database servers today for a
heavily-hit web server. Everything went smoothly for the most part, except
I'm having sporadic queries that are taking an excessive amount of time to
execute. Excessive, as in, before the swap, these queries would take < 2
seconds, and now they are timing out (> 30 seconds). Also, this is only
happening for a very small percentage of cases.
To make things more confusing, I had it happening to me consistently for
about 20 minutes, but when I ran the exact same stored proc in Query
Analyzer, it would run just fine.
So I try to trace it in Profiler. The results were very odd. As expected,
when hit from the website (ADO), my query was timing out (Duration ~30000),
while the trace results from QA were 1-2 seconds. But my reads from ADO
were HUGE -- like in the millions -- whereas the reads from QA were only in
the thousdands.
What in the world would cause my reads to get so high, just by hitting it
from ADO? As I said, it's the exact same stored procedure and parameters (I
actually copied the TextData from Profiler and pasted it into QA).
I'm assuming that the execution plan between the two is different, but I
have no idea how to capture the execution plan from my particular
problem-causing query from the web (without getting flooded w/ all the other
queries running). I was able to capture my QA execution plan in Profile by
filtering on SPID, but my SPID from ADO changes constantly.
Oh, by the way, after beating my head against the wall trying to figure this
out, my query from the web started behaving normally, so my troubleshooting
had to come to an end.
I'm very confused. Please, any suggestions would be appreciated.
Thanks.
JeradJerad
Capture an execution plan from the Profiler and compare it to the execution
plan that was generated by QA.
Can you indentify a stored procedure in the Profiler that caused to the
problem?
"Jerad Rose" <no@.spam.com> wrote in message
news:e2KdzyQhFHA.2644@.TK2MSFTNGP09.phx.gbl...
> Hello.
> I have an odd scenario. I just swapped out database servers today for a
> heavily-hit web server. Everything went smoothly for the most part,
except
> I'm having sporadic queries that are taking an excessive amount of time to
> execute. Excessive, as in, before the swap, these queries would take < 2
> seconds, and now they are timing out (> 30 seconds). Also, this is only
> happening for a very small percentage of cases.
> To make things more confusing, I had it happening to me consistently for
> about 20 minutes, but when I ran the exact same stored proc in Query
> Analyzer, it would run just fine.
> So I try to trace it in Profiler. The results were very odd. As
expected,
> when hit from the website (ADO), my query was timing out (Duration
~30000),
> while the trace results from QA were 1-2 seconds. But my reads from ADO
> were HUGE -- like in the millions -- whereas the reads from QA were only
in
> the thousdands.
> What in the world would cause my reads to get so high, just by hitting it
> from ADO? As I said, it's the exact same stored procedure and parameters
(I
> actually copied the TextData from Profiler and pasted it into QA).
> I'm assuming that the execution plan between the two is different, but I
> have no idea how to capture the execution plan from my particular
> problem-causing query from the web (without getting flooded w/ all the
other
> queries running). I was able to capture my QA execution plan in Profile
by
> filtering on SPID, but my SPID from ADO changes constantly.
> Oh, by the way, after beating my head against the wall trying to figure
this
> out, my query from the web started behaving normally, so my
troubleshooting
> had to come to an end.
> I'm very confused. Please, any suggestions would be appreciated.
> Thanks.
> Jerad
>|||Hi
You may want to look at SP:recompile events.
You may want to try scripting the trace output as a SQL script and running
that.
It could be that you are passing atypical parameter values, so read up on
parameter sniffing such as in Ken Henderson's "The Guru's Guide to SQL
Server Architecture and Internals" ISBN 0-201-70047-6
John
"Jerad Rose" <no@.spam.com> wrote in message
news:e2KdzyQhFHA.2644@.TK2MSFTNGP09.phx.gbl...
> Hello.
> I have an odd scenario. I just swapped out database servers today for a
> heavily-hit web server. Everything went smoothly for the most part,
> except I'm having sporadic queries that are taking an excessive amount of
> time to execute. Excessive, as in, before the swap, these queries would
> take < 2 seconds, and now they are timing out (> 30 seconds). Also, this
> is only happening for a very small percentage of cases.
> To make things more confusing, I had it happening to me consistently for
> about 20 minutes, but when I ran the exact same stored proc in Query
> Analyzer, it would run just fine.
> So I try to trace it in Profiler. The results were very odd. As
> expected, when hit from the website (ADO), my query was timing out
> (Duration ~30000), while the trace results from QA were 1-2 seconds. But
> my reads from ADO were HUGE -- like in the millions -- whereas the reads
> from QA were only in the thousdands.
> What in the world would cause my reads to get so high, just by hitting it
> from ADO? As I said, it's the exact same stored procedure and parameters
> (I actually copied the TextData from Profiler and pasted it into QA).
> I'm assuming that the execution plan between the two is different, but I
> have no idea how to capture the execution plan from my particular
> problem-causing query from the web (without getting flooded w/ all the
> other queries running). I was able to capture my QA execution plan in
> Profile by filtering on SPID, but my SPID from ADO changes constantly.
> Oh, by the way, after beating my head against the wall trying to figure
> this out, my query from the web started behaving normally, so my
> troubleshooting had to come to an end.
> I'm very confused. Please, any suggestions would be appreciated.
> Thanks.
> Jerad
>|||"Jerad Rose" <no@.spam.com> wrote in message
news:e2KdzyQhFHA.2644@.TK2MSFTNGP09.phx.gbl...
> Hello.
> I have an odd scenario. I just swapped out database servers today for a
> heavily-hit web server. Everything went smoothly for the most part,
> except I'm having sporadic queries that are taking an excessive amount of
> time to execute. Excessive, as in, before the swap, these queries would
> take < 2 seconds, and now they are timing out (> 30 seconds). Also, this
> is only happening for a very small percentage of cases.
> To make things more confusing, I had it happening to me consistently for
> about 20 minutes, but when I ran the exact same stored proc in Query
> Analyzer, it would run just fine.
> So I try to trace it in Profiler. The results were very odd. As
> expected, when hit from the website (ADO), my query was timing out
> (Duration ~30000), while the trace results from QA were 1-2 seconds. But
> my reads from ADO were HUGE -- like in the millions -- whereas the reads
> from QA were only in the thousdands.
> What in the world would cause my reads to get so high, just by hitting it
> from ADO? As I said, it's the exact same stored procedure and parameters
> (I actually copied the TextData from Profiler and pasted it into QA).
> I'm assuming that the execution plan between the two is different, but I
> have no idea how to capture the execution plan from my particular
> problem-causing query from the web (without getting flooded w/ all the
> other queries running). I was able to capture my QA execution plan in
> Profile by filtering on SPID, but my SPID from ADO changes constantly.
> Oh, by the way, after beating my head against the wall trying to figure
> this out, my query from the web started behaving normally, so my
> troubleshooting had to come to an end.
> I'm very confused. Please, any suggestions would be appreciated.
>
I agree that the problem is that your executions are getting a different
plan in QA and ADO.
Typically stored procedure invocations will use a saved query plan, but SQL
Client connections have a number of settings which can affect the results of
queries, and so query plans cannot be shared between clients with different
settings. This might account for why you got a different plan in QA than
from ADO.
First, How to reproduce the bad behavor in QA? Open up profiler and you
will see the connection settings for the existing ADO connections in the
Existing Connection event. Copy those out into QA and run them before you
run the query again. This should give you the best shot at reproducting the
query exactly.
Alternatively, you can use ADO. Just paste this into a file on the server
with a .VBS extension
RunCommand.vbs listing
--
sub RunCommand(sql)
dim con
set con = CreateObject("ADODB.Connection")
con.Open "Provider=sqloledb;" & _
"Data Source=(local);" & _
"Initial Catalog=master;" & _
"User ID=stan;Password=lee"
dim rst
set rst = con.Execute(sql)
dim rc
rc = 0
Do While not rst.eof
rc = rc + 1
rst.MoveNext
loop
msgbox "got " & rc & " rows"
end sub
Dim sql
sql = InputBox("Enter SQL")
RunCommand(sql)
--
General fixes include:
Update statistics. Out of date statistics are the number one cause of poor
query performance. Having "swapped out database servers" is a big red flag
here.
Supress Parameter sniffing in your stored procedure by not binding query
where-clause parameters directlly to input parameters. Instead bind the
query to a local variable set to the value of the input parameter.
Run the stored procedure WITH RECOMPILE, you will get better query plans,
for the cost of recompiling the procedure on every invocation.
David|||Are you using a client side or server side cursor from ADO? This can have a
huge impact on the number of reads and the duration, even where the same
execution plan is used as the Query Analyser. Why? Extra reads can come from
the cursoring operations & extra duration from the overall time lapsed to
cursor over the resultset.
If you want to confirm the runtime execution plan, you can inject a SQL
Trace script into the actual command to log execution plans for the current
SPID. This isn't code you'd want to leave permanently in production, just
something you'd add temporarily to get the precise plans during run-time. To
do this, you'd create the trace in profiler & use the File / Script Trace
menu to generate the script. You'd have to manually add the SPID filter via
a parameter you'd pick up at runtime.. If your SQL's embedded into an ASP
page or Win32 app, you might extract it to a stored proc first so that you
can add the trace codemore easily. Sure, this is jumping through a lot of
hoops (it regularly annoys me that this is so hard to do) but at least there
is a way to get the job done.
Regards,
Greg Linwood
SQL Server MVP
"Jerad Rose" <no@.spam.com> wrote in message
news:e2KdzyQhFHA.2644@.TK2MSFTNGP09.phx.gbl...
> Hello.
> I have an odd scenario. I just swapped out database servers today for a
> heavily-hit web server. Everything went smoothly for the most part,
> except I'm having sporadic queries that are taking an excessive amount of
> time to execute. Excessive, as in, before the swap, these queries would
> take < 2 seconds, and now they are timing out (> 30 seconds). Also, this
> is only happening for a very small percentage of cases.
> To make things more confusing, I had it happening to me consistently for
> about 20 minutes, but when I ran the exact same stored proc in Query
> Analyzer, it would run just fine.
> So I try to trace it in Profiler. The results were very odd. As
> expected, when hit from the website (ADO), my query was timing out
> (Duration ~30000), while the trace results from QA were 1-2 seconds. But
> my reads from ADO were HUGE -- like in the millions -- whereas the reads
> from QA were only in the thousdands.
> What in the world would cause my reads to get so high, just by hitting it
> from ADO? As I said, it's the exact same stored procedure and parameters
> (I actually copied the TextData from Profiler and pasted it into QA).
> I'm assuming that the execution plan between the two is different, but I
> have no idea how to capture the execution plan from my particular
> problem-causing query from the web (without getting flooded w/ all the
> other queries running). I was able to capture my QA execution plan in
> Profile by filtering on SPID, but my SPID from ADO changes constantly.
> Oh, by the way, after beating my head against the wall trying to figure
> this out, my query from the web started behaving normally, so my
> troubleshooting had to come to an end.
> I'm very confused. Please, any suggestions would be appreciated.
> Thanks.
> Jerad
>|||How can you tell if its a client or server side cursor ? Whats the
difference between the two ?
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23BfqHwZhFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Are you using a client side or server side cursor from ADO? This can have
a
> huge impact on the number of reads and the duration, even where the same
> execution plan is used as the Query Analyser. Why? Extra reads can come
from
> the cursoring operations & extra duration from the overall time lapsed to
> cursor over the resultset.
> If you want to confirm the runtime execution plan, you can inject a SQL
> Trace script into the actual command to log execution plans for the
current
> SPID. This isn't code you'd want to leave permanently in production, just
> something you'd add temporarily to get the precise plans during run-time.
To
> do this, you'd create the trace in profiler & use the File / Script Trace
> menu to generate the script. You'd have to manually add the SPID filter
via
> a parameter you'd pick up at runtime.. If your SQL's embedded into an ASP
> page or Win32 app, you might extract it to a stored proc first so that you
> can add the trace codemore easily. Sure, this is jumping through a lot of
> hoops (it regularly annoys me that this is so hard to do) but at least
there
> is a way to get the job done.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Jerad Rose" <no@.spam.com> wrote in message
> news:e2KdzyQhFHA.2644@.TK2MSFTNGP09.phx.gbl...
> > Hello.
> >
> > I have an odd scenario. I just swapped out database servers today for a
> > heavily-hit web server. Everything went smoothly for the most part,
> > except I'm having sporadic queries that are taking an excessive amount
of
> > time to execute. Excessive, as in, before the swap, these queries would
> > take < 2 seconds, and now they are timing out (> 30 seconds). Also,
this
> > is only happening for a very small percentage of cases.
> >
> > To make things more confusing, I had it happening to me consistently for
> > about 20 minutes, but when I ran the exact same stored proc in Query
> > Analyzer, it would run just fine.
> >
> > So I try to trace it in Profiler. The results were very odd. As
> > expected, when hit from the website (ADO), my query was timing out
> > (Duration ~30000), while the trace results from QA were 1-2 seconds.
But
> > my reads from ADO were HUGE -- like in the millions -- whereas the reads
> > from QA were only in the thousdands.
> >
> > What in the world would cause my reads to get so high, just by hitting
it
> > from ADO? As I said, it's the exact same stored procedure and
parameters
> > (I actually copied the TextData from Profiler and pasted it into QA).
> >
> > I'm assuming that the execution plan between the two is different, but I
> > have no idea how to capture the execution plan from my particular
> > problem-causing query from the web (without getting flooded w/ all the
> > other queries running). I was able to capture my QA execution plan in
> > Profile by filtering on SPID, but my SPID from ADO changes constantly.
> >
> > Oh, by the way, after beating my head against the wall trying to figure
> > this out, my query from the web started behaving normally, so my
> > troubleshooting had to come to an end.
> >
> > I'm very confused. Please, any suggestions would be appreciated.
> >
> > Thanks.
> > Jerad
> >
>|||A client cursor means that the client application downloads the entire
resultset before moving (eg rs.movenext or dr.read) over the rows. With a
server-side cursor, the client application brings dow rows one at a time
during rs.movenext or dr.read operations.
You can tell the difference by checking ADO's CursorLocation properties on
the resultset or command objects.
btw, queries executed over a client-side cursor can still have much longer
duration than the same queries executed directly on the server via Query
Analyser because the resultset needs to be sent back over the network to the
client application. If the application stalls the process of reading the
resultset for any reason (the cliient pc might be busy running multiple
applications for example), this reflects in the duration value..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23eRt9AdhFHA.1252@.TK2MSFTNGP09.phx.gbl...
> How can you tell if its a client or server side cursor ? Whats the
> difference between the two ?
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:%23BfqHwZhFHA.3316@.TK2MSFTNGP14.phx.gbl...
>> Are you using a client side or server side cursor from ADO? This can have
> a
>> huge impact on the number of reads and the duration, even where the same
>> execution plan is used as the Query Analyser. Why? Extra reads can come
> from
>> the cursoring operations & extra duration from the overall time lapsed to
>> cursor over the resultset.
>> If you want to confirm the runtime execution plan, you can inject a SQL
>> Trace script into the actual command to log execution plans for the
> current
>> SPID. This isn't code you'd want to leave permanently in production, just
>> something you'd add temporarily to get the precise plans during run-time.
> To
>> do this, you'd create the trace in profiler & use the File / Script Trace
>> menu to generate the script. You'd have to manually add the SPID filter
> via
>> a parameter you'd pick up at runtime.. If your SQL's embedded into an ASP
>> page or Win32 app, you might extract it to a stored proc first so that
>> you
>> can add the trace codemore easily. Sure, this is jumping through a lot of
>> hoops (it regularly annoys me that this is so hard to do) but at least
> there
>> is a way to get the job done.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "Jerad Rose" <no@.spam.com> wrote in message
>> news:e2KdzyQhFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> > Hello.
>> >
>> > I have an odd scenario. I just swapped out database servers today for
>> > a
>> > heavily-hit web server. Everything went smoothly for the most part,
>> > except I'm having sporadic queries that are taking an excessive amount
> of
>> > time to execute. Excessive, as in, before the swap, these queries
>> > would
>> > take < 2 seconds, and now they are timing out (> 30 seconds). Also,
> this
>> > is only happening for a very small percentage of cases.
>> >
>> > To make things more confusing, I had it happening to me consistently
>> > for
>> > about 20 minutes, but when I ran the exact same stored proc in Query
>> > Analyzer, it would run just fine.
>> >
>> > So I try to trace it in Profiler. The results were very odd. As
>> > expected, when hit from the website (ADO), my query was timing out
>> > (Duration ~30000), while the trace results from QA were 1-2 seconds.
> But
>> > my reads from ADO were HUGE -- like in the millions -- whereas the
>> > reads
>> > from QA were only in the thousdands.
>> >
>> > What in the world would cause my reads to get so high, just by hitting
> it
>> > from ADO? As I said, it's the exact same stored procedure and
> parameters
>> > (I actually copied the TextData from Profiler and pasted it into QA).
>> >
>> > I'm assuming that the execution plan between the two is different, but
>> > I
>> > have no idea how to capture the execution plan from my particular
>> > problem-causing query from the web (without getting flooded w/ all the
>> > other queries running). I was able to capture my QA execution plan in
>> > Profile by filtering on SPID, but my SPID from ADO changes constantly.
>> >
>> > Oh, by the way, after beating my head against the wall trying to figure
>> > this out, my query from the web started behaving normally, so my
>> > troubleshooting had to come to an end.
>> >
>> > I'm very confused. Please, any suggestions would be appreciated.
>> >
>> > Thanks.
>> > Jerad
>> >
>>
>|||Thanks so much for all the great replies.
It's late, so I have to make this quick.
. I was able to reproduce the slow execution through QA, so I don't think
it's necessarily a QA vs. ADO thing. Sometimes it runs ok in ADO and in QA.
Sometimes it's slow in both (or one or the other). I think this may also
rule out client-side vs. server-side cursors.
. I did run sp_updatestats, but that didn't seem to help much
. I tried WITH RECOMPILE, but didn't seem to help much
. I managed to capture the execution plan for when it runs fast (< 2
seconds) vs. when it times out (> 30 seconds).
Here are the links to those (top lists stats):
http://www.animalcrossingcommunity.com/slowplan.txt
http://www.animalcrossingcommunity.com/fastplan.txt
Here is the actual stored proc that is run (this is actually a snippet --
some stuff runs afterwards, but I'm 99% this part is what is causing the
problems):
http://www.animalcrossingcommunity.com/query.txt
Thanks again for all your help.
Jerad
"Jerad Rose" <no@.spam.com> wrote in message
news:e2KdzyQhFHA.2644@.TK2MSFTNGP09.phx.gbl...
> Hello.
> I have an odd scenario. I just swapped out database servers today for a
> heavily-hit web server. Everything went smoothly for the most part,
> except I'm having sporadic queries that are taking an excessive amount of
> time to execute. Excessive, as in, before the swap, these queries would
> take < 2 seconds, and now they are timing out (> 30 seconds). Also, this
> is only happening for a very small percentage of cases.
> To make things more confusing, I had it happening to me consistently for
> about 20 minutes, but when I ran the exact same stored proc in Query
> Analyzer, it would run just fine.
> So I try to trace it in Profiler. The results were very odd. As
> expected, when hit from the website (ADO), my query was timing out
> (Duration ~30000), while the trace results from QA were 1-2 seconds. But
> my reads from ADO were HUGE -- like in the millions -- whereas the reads
> from QA were only in the thousdands.
> What in the world would cause my reads to get so high, just by hitting it
> from ADO? As I said, it's the exact same stored procedure and parameters
> (I actually copied the TextData from Profiler and pasted it into QA).
> I'm assuming that the execution plan between the two is different, but I
> have no idea how to capture the execution plan from my particular
> problem-causing query from the web (without getting flooded w/ all the
> other queries running). I was able to capture my QA execution plan in
> Profile by filtering on SPID, but my SPID from ADO changes constantly.
> Oh, by the way, after beating my head against the wall trying to figure
> this out, my query from the web started behaving normally, so my
> troubleshooting had to come to an end.
> I'm very confused. Please, any suggestions would be appreciated.
> Thanks.
> Jerad
>|||"Jerad Rose" <no@.spam.com> wrote in message
news:%23l200yehFHA.328@.tk2msftngp13.phx.gbl...
> Thanks so much for all the great replies.
> It's late, so I have to make this quick.
> . I was able to reproduce the slow execution through QA, so I don't think
> it's necessarily a QA vs. ADO thing. Sometimes it runs ok in ADO and in
> QA. Sometimes it's slow in both (or one or the other). I think this may
> also rule out client-side vs. server-side cursors.
> . I did run sp_updatestats, but that didn't seem to help much
> . I tried WITH RECOMPILE, but didn't seem to help much
> . I managed to capture the execution plan for when it runs fast (< 2
> seconds) vs. when it times out (> 30 seconds).
> Here are the links to those (top lists stats):
> http://www.animalcrossingcommunity.com/slowplan.txt
> http://www.animalcrossingcommunity.com/fastplan.txt
> Here is the actual stored proc that is run (this is actually a snippet --
> some stuff runs afterwards, but I'm 99% this part is what is causing the
> problems):
> http://www.animalcrossingcommunity.com/query.txt
> Thanks again for all your help.
>
Good. Now post the table DDL with indexes and a an estimate of the number
of rows in each table.
This is a hard one, and the best plan in cases like this is often to look
beyond these two plans and see if changes to the indexing structure or query
formulation can result in a much cheaper (and therefore more stable) plan.
David
David|||Ok, here you go:
http://www.animalcrossingcommunity.com/ddl.txt
Here are some more clues. Before I swapped servers (basically same specs,
but new has Raid1 configuration and old just had one HD), this particular
query gave me virtually no problems. It typically ran in under a second,
seemingly averaging about 0.5 seconds. Now, even at best, I'm getting 1-2
or more seconds -- at worst, 30+ seconds.
Thanks again for taking time to help. If there's any other info I can
provide to help diagnosis, let me know.
Jerad
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:urYuhAjhFHA.1048@.tk2msftngp13.phx.gbl...
> "Jerad Rose" <no@.spam.com> wrote in message
> news:%23l200yehFHA.328@.tk2msftngp13.phx.gbl...
>> Thanks so much for all the great replies.
>> It's late, so I have to make this quick.
>> . I was able to reproduce the slow execution through QA, so I don't think
>> it's necessarily a QA vs. ADO thing. Sometimes it runs ok in ADO and in
>> QA. Sometimes it's slow in both (or one or the other). I think this may
>> also rule out client-side vs. server-side cursors.
>> . I did run sp_updatestats, but that didn't seem to help much
>> . I tried WITH RECOMPILE, but didn't seem to help much
>> . I managed to capture the execution plan for when it runs fast (< 2
>> seconds) vs. when it times out (> 30 seconds).
>> Here are the links to those (top lists stats):
>> http://www.animalcrossingcommunity.com/slowplan.txt
>> http://www.animalcrossingcommunity.com/fastplan.txt
>> Here is the actual stored proc that is run (this is actually a snippet --
>> some stuff runs afterwards, but I'm 99% this part is what is causing the
>> problems):
>> http://www.animalcrossingcommunity.com/query.txt
>> Thanks again for all your help.
> Good. Now post the table DDL with indexes and a an estimate of the number
> of rows in each table.
> This is a hard one, and the best plan in cases like this is often to look
> beyond these two plans and see if changes to the indexing structure or
> query formulation can result in a much cheaper (and therefore more stable)
> plan.
> David
> David
>|||"Jerad Rose" <no@.spam.com> wrote in message
news:OOfwlLjhFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Ok, here you go:
> http://www.animalcrossingcommunity.com/ddl.txt
> Here are some more clues. Before I swapped servers (basically same specs,
> but new has Raid1 configuration and old just had one HD), this particular
> query gave me virtually no problems. It typically ran in under a second,
> seemingly averaging about 0.5 seconds. Now, even at best, I'm getting 1-2
> or more seconds -- at worst, 30+ seconds.
> Thanks again for taking time to help. If there's any other info I can
> provide to help diagnosis, let me know.
> Jerad
>
No smoking gun, but here's some things to try:
--ThreadID is the leading column in the clustered index. This index is
duplicative.
drop index ThreadUser.IX_ThreadUser
--Foregn keys should be indexed, and linking tables should be indexed
--for 2-way access
create index IX_PrivateThreadUser_User on PrivateThreaduser(UserID)
--Try a compund index to support this search
create index IX_ThreadSearch on Thread(ThreadModifyDateTime
desc,ThreadIsDeleted,ThreadIsLocked)
--and modify the procedure to not suppress index usage on
ThreadModifyDateTime
--change
and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime + 1 >
getdate())
--to
and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime >
datediff(d,-1,getdate()))
David|||Wow.
Ok, so I have NO idea why I didn't already have UserID indexed on
PrivateThread. That could probably explain most of the issues -- and why I
was approaching reads in the millions. Good catch. The others made perfect
sense as well.
So, I've got all of your suggestions implmeneted, and so far it seems to
have made a big difference. So much thanks again for your time and help!
My only confusion now, is, why this didn't seem to be a problem on the old
server. Especially considering my lack of an index for UserID on
PrivateThread, I'm surprised this didn't give me trouble before.
I've been running a trace, and so far, reads have not exceeded 10,000 and
duration is less than a second except on a few rare occasions where it hits
a few seconds -- so that seems to have fixed my problem so far. Now I'm a
little concerned of how many other spots have inadequate indexing.
Thanks again David (and others) for your time and help. It has been much
appreciated.
Jerad
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23oWyIhjhFHA.320@.TK2MSFTNGP09.phx.gbl...
> "Jerad Rose" <no@.spam.com> wrote in message
> news:OOfwlLjhFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Ok, here you go:
>> http://www.animalcrossingcommunity.com/ddl.txt
>> Here are some more clues. Before I swapped servers (basically same
>> specs, but new has Raid1 configuration and old just had one HD), this
>> particular query gave me virtually no problems. It typically ran in
>> under a second, seemingly averaging about 0.5 seconds. Now, even at
>> best, I'm getting 1-2 or more seconds -- at worst, 30+ seconds.
>> Thanks again for taking time to help. If there's any other info I can
>> provide to help diagnosis, let me know.
>> Jerad
> No smoking gun, but here's some things to try:
> --ThreadID is the leading column in the clustered index. This index is
> duplicative.
> drop index ThreadUser.IX_ThreadUser
> --Foregn keys should be indexed, and linking tables should be indexed
> --for 2-way access
> create index IX_PrivateThreadUser_User on PrivateThreaduser(UserID)
> --Try a compund index to support this search
> create index IX_ThreadSearch on Thread(ThreadModifyDateTime
> desc,ThreadIsDeleted,ThreadIsLocked)
> --and modify the procedure to not suppress index usage on
> ThreadModifyDateTime
> --change
> and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime + 1 >
> getdate())
> --to
> and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime >
> datediff(d,-1,getdate()))
> David
>|||>> I've been running a trace, and so far, reads have not exceeded 10,000 and
Is that for a single query? If so then you aren't done yet<g>. Unless you
are pulling back thousands of rows (which you should question why) you
should be able to do this in a few hundred reads at best with proper
indexing and WHERE clauses. There are exceptions but that many reads
indicates either a full or partial scan. You should find out if that is the
properly indexed as well.
Andrew J. Kelly SQL MVP
"Jerad Rose" <no@.spam.com> wrote in message
news:%23WPIMyjhFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Wow.
> Ok, so I have NO idea why I didn't already have UserID indexed on
> PrivateThread. That could probably explain most of the issues -- and why
> I was approaching reads in the millions. Good catch. The others made
> perfect sense as well.
> So, I've got all of your suggestions implmeneted, and so far it seems to
> have made a big difference. So much thanks again for your time and help!
> My only confusion now, is, why this didn't seem to be a problem on the old
> server. Especially considering my lack of an index for UserID on
> PrivateThread, I'm surprised this didn't give me trouble before.
> I've been running a trace, and so far, reads have not exceeded 10,000 and
> duration is less than a second except on a few rare occasions where it
> hits a few seconds -- so that seems to have fixed my problem so far. Now
> I'm a little concerned of how many other spots have inadequate indexing.
> Thanks again David (and others) for your time and help. It has been much
> appreciated.
> Jerad
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23oWyIhjhFHA.320@.TK2MSFTNGP09.phx.gbl...
>> "Jerad Rose" <no@.spam.com> wrote in message
>> news:OOfwlLjhFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Ok, here you go:
>> http://www.animalcrossingcommunity.com/ddl.txt
>> Here are some more clues. Before I swapped servers (basically same
>> specs, but new has Raid1 configuration and old just had one HD), this
>> particular query gave me virtually no problems. It typically ran in
>> under a second, seemingly averaging about 0.5 seconds. Now, even at
>> best, I'm getting 1-2 or more seconds -- at worst, 30+ seconds.
>> Thanks again for taking time to help. If there's any other info I can
>> provide to help diagnosis, let me know.
>> Jerad
>> No smoking gun, but here's some things to try:
>> --ThreadID is the leading column in the clustered index. This index is
>> duplicative.
>> drop index ThreadUser.IX_ThreadUser
>> --Foregn keys should be indexed, and linking tables should be indexed
>> --for 2-way access
>> create index IX_PrivateThreadUser_User on PrivateThreaduser(UserID)
>> --Try a compund index to support this search
>> create index IX_ThreadSearch on Thread(ThreadModifyDateTime
>> desc,ThreadIsDeleted,ThreadIsLocked)
>> --and modify the procedure to not suppress index usage on
>> ThreadModifyDateTime
>> --change
>> and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime + 1 >
>> getdate())
>> --to
>> and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime >
>> datediff(d,-1,getdate()))
>> David
>|||Hi Andrew.
Well, no, it's a few queries. I have one query that loads ThreadIDs into a
temp table, which I later hit for paging. For example, the first query (the
one we've been dealing with) pulls all threads for a particular user, and
stores the ID along w/ a sequential identity column into a temp table.
Then, I have a second query that joins the temp table with the rest of my
tables to return a set of records -- say, records 201-250 (page 5). So, the
first query could potentially return thousands of records.
I'm not going to say that I still don't have some inadequate indexing, but
I'm much happier with the results I have now than what I was getting before.
But feel free to offer any other suggestions, based on the info in the above
links. If you need additional info other than what has been posted, let me
know.
Thanks again.
Jerad
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eB3rR7jhFHA.1164@.TK2MSFTNGP10.phx.gbl...
>> I've been running a trace, and so far, reads have not exceeded 10,000
>> and
> Is that for a single query? If so then you aren't done yet<g>. Unless
> you are pulling back thousands of rows (which you should question why) you
> should be able to do this in a few hundred reads at best with proper
> indexing and WHERE clauses. There are exceptions but that many reads
> indicates either a full or partial scan. You should find out if that is
> the properly indexed as well.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Jerad Rose" <no@.spam.com> wrote in message
> news:%23WPIMyjhFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Wow.
>> Ok, so I have NO idea why I didn't already have UserID indexed on
>> PrivateThread. That could probably explain most of the issues -- and why
>> I was approaching reads in the millions. Good catch. The others made
>> perfect sense as well.
>> So, I've got all of your suggestions implmeneted, and so far it seems to
>> have made a big difference. So much thanks again for your time and help!
>> My only confusion now, is, why this didn't seem to be a problem on the
>> old server. Especially considering my lack of an index for UserID on
>> PrivateThread, I'm surprised this didn't give me trouble before.
>> I've been running a trace, and so far, reads have not exceeded 10,000 and
>> duration is less than a second except on a few rare occasions where it
>> hits a few seconds -- so that seems to have fixed my problem so far. Now
>> I'm a little concerned of how many other spots have inadequate indexing.
>> Thanks again David (and others) for your time and help. It has been much
>> appreciated.
>> Jerad
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:%23oWyIhjhFHA.320@.TK2MSFTNGP09.phx.gbl...
>> "Jerad Rose" <no@.spam.com> wrote in message
>> news:OOfwlLjhFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Ok, here you go:
>> http://www.animalcrossingcommunity.com/ddl.txt
>> Here are some more clues. Before I swapped servers (basically same
>> specs, but new has Raid1 configuration and old just had one HD), this
>> particular query gave me virtually no problems. It typically ran in
>> under a second, seemingly averaging about 0.5 seconds. Now, even at
>> best, I'm getting 1-2 or more seconds -- at worst, 30+ seconds.
>> Thanks again for taking time to help. If there's any other info I can
>> provide to help diagnosis, let me know.
>> Jerad
>> No smoking gun, but here's some things to try:
>> --ThreadID is the leading column in the clustered index. This index is
>> duplicative.
>> drop index ThreadUser.IX_ThreadUser
>> --Foregn keys should be indexed, and linking tables should be indexed
>> --for 2-way access
>> create index IX_PrivateThreadUser_User on PrivateThreaduser(UserID)
>> --Try a compund index to support this search
>> create index IX_ThreadSearch on Thread(ThreadModifyDateTime
>> desc,ThreadIsDeleted,ThreadIsLocked)
>> --and modify the procedure to not suppress index usage on
>> ThreadModifyDateTime
>> --change
>> and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime + 1 >
>> getdate())
>> --to
>> and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime >
>> datediff(d,-1,getdate()))
>> David
>>
>|||Ok, another update.
Unfortunately, it looks like this only helped temporarily. It's almost as
if I perform some sort of "reset" (or maybe updating the statistics), and it
works for a while, but then my indexes get outdated and start requiring
several reads once again.
I just had one trace that hit over 11 million reads. This just doesn't make
sense when 30 minutes ago, this exact same query only had a couple thousand
reads.
Stiff confused (and frustrated).
Thanks again for any suggestions.
Jerad
"Jerad Rose" <no@.spam.com> wrote in message
news:%23WPIMyjhFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Wow.
> Ok, so I have NO idea why I didn't already have UserID indexed on
> PrivateThread. That could probably explain most of the issues -- and why
> I was approaching reads in the millions. Good catch. The others made
> perfect sense as well.
> So, I've got all of your suggestions implmeneted, and so far it seems to
> have made a big difference. So much thanks again for your time and help!
> My only confusion now, is, why this didn't seem to be a problem on the old
> server. Especially considering my lack of an index for UserID on
> PrivateThread, I'm surprised this didn't give me trouble before.
> I've been running a trace, and so far, reads have not exceeded 10,000 and
> duration is less than a second except on a few rare occasions where it
> hits a few seconds -- so that seems to have fixed my problem so far. Now
> I'm a little concerned of how many other spots have inadequate indexing.
> Thanks again David (and others) for your time and help. It has been much
> appreciated.
> Jerad
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23oWyIhjhFHA.320@.TK2MSFTNGP09.phx.gbl...
>> "Jerad Rose" <no@.spam.com> wrote in message
>> news:OOfwlLjhFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Ok, here you go:
>> http://www.animalcrossingcommunity.com/ddl.txt
>> Here are some more clues. Before I swapped servers (basically same
>> specs, but new has Raid1 configuration and old just had one HD), this
>> particular query gave me virtually no problems. It typically ran in
>> under a second, seemingly averaging about 0.5 seconds. Now, even at
>> best, I'm getting 1-2 or more seconds -- at worst, 30+ seconds.
>> Thanks again for taking time to help. If there's any other info I can
>> provide to help diagnosis, let me know.
>> Jerad
>> No smoking gun, but here's some things to try:
>> --ThreadID is the leading column in the clustered index. This index is
>> duplicative.
>> drop index ThreadUser.IX_ThreadUser
>> --Foregn keys should be indexed, and linking tables should be indexed
>> --for 2-way access
>> create index IX_PrivateThreadUser_User on PrivateThreaduser(UserID)
>> --Try a compund index to support this search
>> create index IX_ThreadSearch on Thread(ThreadModifyDateTime
>> desc,ThreadIsDeleted,ThreadIsLocked)
>> --and modify the procedure to not suppress index usage on
>> ThreadModifyDateTime
>> --change
>> and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime + 1 >
>> getdate())
>> --to
>> and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime >
>> datediff(d,-1,getdate()))
>> David
>|||The number of rows affected is one of the criteria for the use of an index
as a scan or a seek. If your query is not reusing a query plan it will
generate a new one each time you call it. It uses the values you supply to
determine what the plan should be. If this time the number of rows was too
much for a seek it might choose to do a scan. Some of the values for that
query will be higher than others in the number of rows returned. I haven't
spent a lot of time looking at your query and DDL but that join is fairly
complex and has several LEFT JOINS in it that probably don't help. Which
table is doing the most reads? You might want to be sure there is a
clustered index on the column that you are doing the join or range scan on.
--
Andrew J. Kelly SQL MVP
"Jerad Rose" <no@.spam.com> wrote in message
news:uLgXBHkhFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Ok, another update.
> Unfortunately, it looks like this only helped temporarily. It's almost as
> if I perform some sort of "reset" (or maybe updating the statistics), and
> it works for a while, but then my indexes get outdated and start requiring
> several reads once again.
> I just had one trace that hit over 11 million reads. This just doesn't
> make sense when 30 minutes ago, this exact same query only had a couple
> thousand reads.
> Stiff confused (and frustrated).
> Thanks again for any suggestions.
> Jerad
> "Jerad Rose" <no@.spam.com> wrote in message
> news:%23WPIMyjhFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Wow.
>> Ok, so I have NO idea why I didn't already have UserID indexed on
>> PrivateThread. That could probably explain most of the issues -- and why
>> I was approaching reads in the millions. Good catch. The others made
>> perfect sense as well.
>> So, I've got all of your suggestions implmeneted, and so far it seems to
>> have made a big difference. So much thanks again for your time and help!
>> My only confusion now, is, why this didn't seem to be a problem on the
>> old server. Especially considering my lack of an index for UserID on
>> PrivateThread, I'm surprised this didn't give me trouble before.
>> I've been running a trace, and so far, reads have not exceeded 10,000 and
>> duration is less than a second except on a few rare occasions where it
>> hits a few seconds -- so that seems to have fixed my problem so far. Now
>> I'm a little concerned of how many other spots have inadequate indexing.
>> Thanks again David (and others) for your time and help. It has been much
>> appreciated.
>> Jerad
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:%23oWyIhjhFHA.320@.TK2MSFTNGP09.phx.gbl...
>> "Jerad Rose" <no@.spam.com> wrote in message
>> news:OOfwlLjhFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Ok, here you go:
>> http://www.animalcrossingcommunity.com/ddl.txt
>> Here are some more clues. Before I swapped servers (basically same
>> specs, but new has Raid1 configuration and old just had one HD), this
>> particular query gave me virtually no problems. It typically ran in
>> under a second, seemingly averaging about 0.5 seconds. Now, even at
>> best, I'm getting 1-2 or more seconds -- at worst, 30+ seconds.
>> Thanks again for taking time to help. If there's any other info I can
>> provide to help diagnosis, let me know.
>> Jerad
>> No smoking gun, but here's some things to try:
>> --ThreadID is the leading column in the clustered index. This index is
>> duplicative.
>> drop index ThreadUser.IX_ThreadUser
>> --Foregn keys should be indexed, and linking tables should be indexed
>> --for 2-way access
>> create index IX_PrivateThreadUser_User on PrivateThreaduser(UserID)
>> --Try a compund index to support this search
>> create index IX_ThreadSearch on Thread(ThreadModifyDateTime
>> desc,ThreadIsDeleted,ThreadIsLocked)
>> --and modify the procedure to not suppress index usage on
>> ThreadModifyDateTime
>> --change
>> and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime + 1 >
>> getdate())
>> --to
>> and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime >
>> datediff(d,-1,getdate()))
>> David
>>
>|||I didn't provide the entire stored proc, as I didn't want to muddy the
waters with too much information. But as I think about it, it may have to
do with the fact that I have several sets of "first queries", that are ran
based on the parameters (namely BoardID). Maybe that's causing the
execution plan to be inneficient.
Here is the link to the entire proc, and you'll see what I'm talking about:
http://www.animalcrossingcommunity.com/proc.txt
I did this to prevent from having to have redundant code in many procs, but
I will much rather have redundant code if it meant better performance.
Take a look at that and let me know if you think I'm on the right track.
Thanks again.
Jerad
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eXF4ltkhFHA.1968@.TK2MSFTNGP14.phx.gbl...
> The number of rows affected is one of the criteria for the use of an index
> as a scan or a seek. If your query is not reusing a query plan it will
> generate a new one each time you call it. It uses the values you supply to
> determine what the plan should be. If this time the number of rows was
> too much for a seek it might choose to do a scan. Some of the values for
> that query will be higher than others in the number of rows returned. I
> haven't spent a lot of time looking at your query and DDL but that join is
> fairly complex and has several LEFT JOINS in it that probably don't help.
> Which table is doing the most reads? You might want to be sure there is a
> clustered index on the column that you are doing the join or range scan
> on.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerad Rose" <no@.spam.com> wrote in message
> news:uLgXBHkhFHA.3300@.TK2MSFTNGP15.phx.gbl...
>> Ok, another update.
>> Unfortunately, it looks like this only helped temporarily. It's almost
>> as if I perform some sort of "reset" (or maybe updating the statistics),
>> and it works for a while, but then my indexes get outdated and start
>> requiring several reads once again.
>> I just had one trace that hit over 11 million reads. This just doesn't
>> make sense when 30 minutes ago, this exact same query only had a couple
>> thousand reads.
>> Stiff confused (and frustrated).
>> Thanks again for any suggestions.
>> Jerad
>> "Jerad Rose" <no@.spam.com> wrote in message
>> news:%23WPIMyjhFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Wow.
>> Ok, so I have NO idea why I didn't already have UserID indexed on
>> PrivateThread. That could probably explain most of the issues -- and
>> why I was approaching reads in the millions. Good catch. The others
>> made perfect sense as well.
>> So, I've got all of your suggestions implmeneted, and so far it seems to
>> have made a big difference. So much thanks again for your time and
>> help!
>> My only confusion now, is, why this didn't seem to be a problem on the
>> old server. Especially considering my lack of an index for UserID on
>> PrivateThread, I'm surprised this didn't give me trouble before.
>> I've been running a trace, and so far, reads have not exceeded 10,000
>> and duration is less than a second except on a few rare occasions where
>> it hits a few seconds -- so that seems to have fixed my problem so far.
>> Now I'm a little concerned of how many other spots have inadequate
>> indexing.
>> Thanks again David (and others) for your time and help. It has been
>> much appreciated.
>> Jerad
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:%23oWyIhjhFHA.320@.TK2MSFTNGP09.phx.gbl...
>> "Jerad Rose" <no@.spam.com> wrote in message
>> news:OOfwlLjhFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Ok, here you go:
>> http://www.animalcrossingcommunity.com/ddl.txt
>> Here are some more clues. Before I swapped servers (basically same
>> specs, but new has Raid1 configuration and old just had one HD), this
>> particular query gave me virtually no problems. It typically ran in
>> under a second, seemingly averaging about 0.5 seconds. Now, even at
>> best, I'm getting 1-2 or more seconds -- at worst, 30+ seconds.
>> Thanks again for taking time to help. If there's any other info I can
>> provide to help diagnosis, let me know.
>> Jerad
>> No smoking gun, but here's some things to try:
>> --ThreadID is the leading column in the clustered index. This index is
>> duplicative.
>> drop index ThreadUser.IX_ThreadUser
>> --Foregn keys should be indexed, and linking tables should be indexed
>> --for 2-way access
>> create index IX_PrivateThreadUser_User on PrivateThreaduser(UserID)
>> --Try a compund index to support this search
>> create index IX_ThreadSearch on Thread(ThreadModifyDateTime
>> desc,ThreadIsDeleted,ThreadIsLocked)
>> --and modify the procedure to not suppress index usage on
>> ThreadModifyDateTime
>> --change
>> and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime + 1 >
>> getdate())
>> --to
>> and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime >
>> datediff(d,-1,getdate()))
>> David
>>
>>
>|||"Jerad Rose" <no@.spam.com> wrote in message
news:OhEInElhFHA.1460@.tk2msftngp13.phx.gbl...
>I didn't provide the entire stored proc, as I didn't want to muddy the
>waters with too much information. But as I think about it, it may have to
>do with the fact that I have several sets of "first queries", that are ran
>based on the parameters (namely BoardID). Maybe that's causing the
>execution plan to be inneficient.
> Here is the link to the entire proc, and you'll see what I'm talking
> about:
> http://www.animalcrossingcommunity.com/proc.txt
> I did this to prevent from having to have redundant code in many procs,
> but I will much rather have redundant code if it meant better performance.
> Take a look at that and let me know if you think I'm on the right track.
> Thanks again.
> Jerad
>
Ok, I would further break down the problem query by @.BoardID since the
query logic differs in the two cases. That way SQL doesn't have to use the
same plan for both cases.
else if @.BoardID = 63
insert into @.TempTable(RowID)
select Thread.ThreadID
from Thread (nolock)
join Users (nolock)
on Users.UserID = Thread.UserID
join UserType
on UserType.UserTypeID = @.UserTypeID
left join (
select ThreadID
, min(UserTypeRank) MinUserTypeRank
from PrivateThreadUser (nolock)
join Users (nolock)
on Users.UserID = PrivateThreadUser.UserID
join UserType
on UserType.UserTypeID = Users.UserTypeID
group by ThreadID
) PrivateThreadUserMinRank
on PrivateThreadUserMinRank.ThreadID = Thread.ThreadID
left join PrivateThreadUser (nolock)
on PrivateThreadUser.ThreadID = Thread.ThreadID
and PrivateThreadUser.UserID = @.UserID
left join ThreadUser (nolock)
on ThreadUser.ThreadID = Thread.ThreadID
and ThreadUser.UserID = @.UserID
where
Thread.BoardID = 63
and Thread.ThreadIsDeleted = 0
and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime >
dateadd(d,-1,getdate()))
and (ThreadUser.ThreadIsDeleted is null or ThreadUser.ThreadIsDeleted = 0 )
order by
Thread.ThreadIsSticky desc
, Thread.ThreadLastPostDateTime desc
else if @.BoardID = 53
insert into @.TempTable(RowID)
select Thread.ThreadID
from Thread (nolock)
join Users (nolock)
on Users.UserID = Thread.UserID
join UserType
on UserType.UserTypeID = @.UserTypeID
left join (
select ThreadID
, min(UserTypeRank) MinUserTypeRank
from PrivateThreadUser (nolock)
join Users (nolock)
on Users.UserID = PrivateThreadUser.UserID
join UserType
on UserType.UserTypeID = Users.UserTypeID
group by ThreadID
) PrivateThreadUserMinRank
on PrivateThreadUserMinRank.ThreadID = Thread.ThreadID
left join PrivateThreadUser (nolock)
on PrivateThreadUser.ThreadID = Thread.ThreadID
and PrivateThreadUser.UserID = @.UserID
left join ThreadUser (nolock)
on ThreadUser.ThreadID = Thread.ThreadID
and ThreadUser.UserID = @.UserID
where
Thread.BoardID = 36
and Thread.ThreadIsDeleted = 0
and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime >
dateadd(d,-1,getdate()))
and (PrivateThreadUser.ThreadID is not null or @.BoardID <> 36)
and (PrivateThreadUserMinRank.MinUserTypeRank > UserType.UserTypeRank)
order by
Thread.ThreadIsSticky desc
, Thread.ThreadLastPostDateTime desc
David|||Wow, Well I don't know what to tell you from here. There is no way for me
to tell how efficient these queries are without actually being connected to
your system. Each one of those IF blocks can certainly have a different
query plan and it gets quite convoluted when all lumped together in one sp.
You might consider breaking them into individual sp's and calling the
appropriate one to begin with. Do you really need to do that whole join
twice? Can't you grab what info is relevant the first time and avoid some
of the joins in the second pass?
--
Andrew J. Kelly SQL MVP
"Jerad Rose" <no@.spam.com> wrote in message
news:OhEInElhFHA.1460@.tk2msftngp13.phx.gbl...
>I didn't provide the entire stored proc, as I didn't want to muddy the
>waters with too much information. But as I think about it, it may have to
>do with the fact that I have several sets of "first queries", that are ran
>based on the parameters (namely BoardID). Maybe that's causing the
>execution plan to be inneficient.
> Here is the link to the entire proc, and you'll see what I'm talking
> about:
> http://www.animalcrossingcommunity.com/proc.txt
> I did this to prevent from having to have redundant code in many procs,
> but I will much rather have redundant code if it meant better performance.
> Take a look at that and let me know if you think I'm on the right track.
> Thanks again.
> Jerad
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eXF4ltkhFHA.1968@.TK2MSFTNGP14.phx.gbl...
>> The number of rows affected is one of the criteria for the use of an
>> index as a scan or a seek. If your query is not reusing a query plan it
>> will generate a new one each time you call it. It uses the values you
>> supply to determine what the plan should be. If this time the number of
>> rows was too much for a seek it might choose to do a scan. Some of the
>> values for that query will be higher than others in the number of rows
>> returned. I haven't spent a lot of time looking at your query and DDL
>> but that join is fairly complex and has several LEFT JOINS in it that
>> probably don't help. Which table is doing the most reads? You might want
>> to be sure there is a clustered index on the column that you are doing
>> the join or range scan on.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerad Rose" <no@.spam.com> wrote in message
>> news:uLgXBHkhFHA.3300@.TK2MSFTNGP15.phx.gbl...
>> Ok, another update.
>> Unfortunately, it looks like this only helped temporarily. It's almost
>> as if I perform some sort of "reset" (or maybe updating the statistics),
>> and it works for a while, but then my indexes get outdated and start
>> requiring several reads once again.
>> I just had one trace that hit over 11 million reads. This just doesn't
>> make sense when 30 minutes ago, this exact same query only had a couple
>> thousand reads.
>> Stiff confused (and frustrated).
>> Thanks again for any suggestions.
>> Jerad
>> "Jerad Rose" <no@.spam.com> wrote in message
>> news:%23WPIMyjhFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Wow.
>> Ok, so I have NO idea why I didn't already have UserID indexed on
>> PrivateThread. That could probably explain most of the issues -- and
>> why I was approaching reads in the millions. Good catch. The others
>> made perfect sense as well.
>> So, I've got all of your suggestions implmeneted, and so far it seems
>> to have made a big difference. So much thanks again for your time and
>> help!
>> My only confusion now, is, why this didn't seem to be a problem on the
>> old server. Especially considering my lack of an index for UserID on
>> PrivateThread, I'm surprised this didn't give me trouble before.
>> I've been running a trace, and so far, reads have not exceeded 10,000
>> and duration is less than a second except on a few rare occasions where
>> it hits a few seconds -- so that seems to have fixed my problem so far.
>> Now I'm a little concerned of how many other spots have inadequate
>> indexing.
>> Thanks again David (and others) for your time and help. It has been
>> much appreciated.
>> Jerad
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:%23oWyIhjhFHA.320@.TK2MSFTNGP09.phx.gbl...
>> "Jerad Rose" <no@.spam.com> wrote in message
>> news:OOfwlLjhFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Ok, here you go:
>> http://www.animalcrossingcommunity.com/ddl.txt
>> Here are some more clues. Before I swapped servers (basically same
>> specs, but new has Raid1 configuration and old just had one HD), this
>> particular query gave me virtually no problems. It typically ran in
>> under a second, seemingly averaging about 0.5 seconds. Now, even at
>> best, I'm getting 1-2 or more seconds -- at worst, 30+ seconds.
>> Thanks again for taking time to help. If there's any other info I
>> can provide to help diagnosis, let me know.
>> Jerad
>> No smoking gun, but here's some things to try:
>> --ThreadID is the leading column in the clustered index. This index is
>> duplicative.
>> drop index ThreadUser.IX_ThreadUser
>> --Foregn keys should be indexed, and linking tables should be indexed
>> --for 2-way access
>> create index IX_PrivateThreadUser_User on PrivateThreaduser(UserID)
>> --Try a compund index to support this search
>> create index IX_ThreadSearch on Thread(ThreadModifyDateTime
>> desc,ThreadIsDeleted,ThreadIsLocked)
>> --and modify the procedure to not suppress index usage on
>> ThreadModifyDateTime
>> --change
>> and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime + 1 >
>> getdate())
>> --to
>> and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime >
>> datediff(d,-1,getdate()))
>> David
>>
>>
>>
>|||Hey guys.
I went ahead and split that proc into multiple procs, so that there were no
IF blocks. This seems to have helped a great deal, as this proc is no
longer taking more than a second or two (worst case). It's been running
like this for about a day, so hopefully this was the answer. I'm still
getting a few thousand reads, but I'm not sure I'll be able to get it down
any less than that.
Thanks again for all your help. This has been a good learning experience
for me, as I now feel better equipped to diagnose problem queries and
indexing issues such as this.
Jerad
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ODOgF4lhFHA.3436@.tk2msftngp13.phx.gbl...
> Wow, Well I don't know what to tell you from here. There is no way for me
> to tell how efficient these queries are without actually being connected
> to your system. Each one of those IF blocks can certainly have a
> different query plan and it gets quite convoluted when all lumped together
> in one sp. You might consider breaking them into individual sp's and
> calling the appropriate one to begin with. Do you really need to do that
> whole join twice? Can't you grab what info is relevant the first time and
> avoid some of the joins in the second pass?
> --
> Andrew J. Kelly SQL MVP
>
> "Jerad Rose" <no@.spam.com> wrote in message
> news:OhEInElhFHA.1460@.tk2msftngp13.phx.gbl...
>>I didn't provide the entire stored proc, as I didn't want to muddy the
>>waters with too much information. But as I think about it, it may have to
>>do with the fact that I have several sets of "first queries", that are ran
>>based on the parameters (namely BoardID). Maybe that's causing the
>>execution plan to be inneficient.
>> Here is the link to the entire proc, and you'll see what I'm talking
>> about:
>> http://www.animalcrossingcommunity.com/proc.txt
>> I did this to prevent from having to have redundant code in many procs,
>> but I will much rather have redundant code if it meant better
>> performance.
>> Take a look at that and let me know if you think I'm on the right track.
>> Thanks again.
>> Jerad
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eXF4ltkhFHA.1968@.TK2MSFTNGP14.phx.gbl...
>> The number of rows affected is one of the criteria for the use of an
>> index as a scan or a seek. If your query is not reusing a query plan it
>> will generate a new one each time you call it. It uses the values you
>> supply to determine what the plan should be. If this time the number of
>> rows was too much for a seek it might choose to do a scan. Some of the
>> values for that query will be higher than others in the number of rows
>> returned. I haven't spent a lot of time looking at your query and DDL
>> but that join is fairly complex and has several LEFT JOINS in it that
>> probably don't help. Which table is doing the most reads? You might
>> want to be sure there is a clustered index on the column that you are
>> doing the join or range scan on.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerad Rose" <no@.spam.com> wrote in message
>> news:uLgXBHkhFHA.3300@.TK2MSFTNGP15.phx.gbl...
>> Ok, another update.
>> Unfortunately, it looks like this only helped temporarily. It's almost
>> as if I perform some sort of "reset" (or maybe updating the
>> statistics), and it works for a while, but then my indexes get outdated
>> and start requiring several reads once again.
>> I just had one trace that hit over 11 million reads. This just doesn't
>> make sense when 30 minutes ago, this exact same query only had a couple
>> thousand reads.
>> Stiff confused (and frustrated).
>> Thanks again for any suggestions.
>> Jerad
>> "Jerad Rose" <no@.spam.com> wrote in message
>> news:%23WPIMyjhFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Wow.
>> Ok, so I have NO idea why I didn't already have UserID indexed on
>> PrivateThread. That could probably explain most of the issues -- and
>> why I was approaching reads in the millions. Good catch. The others
>> made perfect sense as well.
>> So, I've got all of your suggestions implmeneted, and so far it seems
>> to have made a big difference. So much thanks again for your time and
>> help!
>> My only confusion now, is, why this didn't seem to be a problem on the
>> old server. Especially considering my lack of an index for UserID on
>> PrivateThread, I'm surprised this didn't give me trouble before.
>> I've been running a trace, and so far, reads have not exceeded 10,000
>> and duration is less than a second except on a few rare occasions
>> where it hits a few seconds -- so that seems to have fixed my problem
>> so far. Now I'm a little concerned of how many other spots have
>> inadequate indexing.
>> Thanks again David (and others) for your time and help. It has been
>> much appreciated.
>> Jerad
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:%23oWyIhjhFHA.320@.TK2MSFTNGP09.phx.gbl...
>> "Jerad Rose" <no@.spam.com> wrote in message
>> news:OOfwlLjhFHA.2072@.TK2MSFTNGP14.phx.gbl...
>>> Ok, here you go:
>>>
>>> http://www.animalcrossingcommunity.com/ddl.txt
>>>
>>> Here are some more clues. Before I swapped servers (basically same
>>> specs, but new has Raid1 configuration and old just had one HD),
>>> this particular query gave me virtually no problems. It typically
>>> ran in under a second, seemingly averaging about 0.5 seconds. Now,
>>> even at best, I'm getting 1-2 or more seconds -- at worst, 30+
>>> seconds.
>>>
>>> Thanks again for taking time to help. If there's any other info I
>>> can provide to help diagnosis, let me know.
>>>
>>> Jerad
>>>
>> No smoking gun, but here's some things to try:
>> --ThreadID is the leading column in the clustered index. This index
>> is duplicative.
>> drop index ThreadUser.IX_ThreadUser
>> --Foregn keys should be indexed, and linking tables should be indexed
>> --for 2-way access
>> create index IX_PrivateThreadUser_User on PrivateThreaduser(UserID)
>> --Try a compund index to support this search
>> create index IX_ThreadSearch on Thread(ThreadModifyDateTime
>> desc,ThreadIsDeleted,ThreadIsLocked)
>> --and modify the procedure to not suppress index usage on
>> ThreadModifyDateTime
>> --change
>> and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime + 1 >
>> getdate())
>> --to
>> and (Thread.ThreadIsLocked = 0 or Thread.ThreadModifyDateTime >
>> datediff(d,-1,getdate()))
>> David
>>
>>
>>
>>
>

Friday, March 30, 2012

Recipe for a good, solid maintenance plan

Receiving queue does not fire stored procedure

Wednesday, March 28, 2012

Receive alerts on SQLServer service up/down status

Monday, March 26, 2012

recbase.cpp:1378 & somerge.c:1616 Errors

recbase.cpp:1378 & somerge.c:1616 Errors

Wednesday, March 21, 2012

Rebuild one node from SQL Cluster

Rebuild one node from SQL Cluster

Rebuild one node from SQL Cluster

Rebuild master.dbo.sysperfinfo

Rebuild master.dbo.sysperfinfo

Monday, March 12, 2012

Rebooting Mirrored Servers

Rebooting Cluster Boxes

Wednesday, March 7, 2012

really simple question

really isolated

Saturday, February 25, 2012

Real time replication over geographically divers sites

Monday, February 20, 2012

Reads/Duration Higher on ADO vs. QA

Reads/Duration Higher on ADO vs. QA

Reads/Duration Higher on ADO vs. QA

Real Time DSS

Blog Archive

About Me