April 30, 2002 at 9:47 am
I am working on a 300GB database. Many times our procedures goes to loop....
I have checked performance monitor & sql profilor and verified that disk i/o or memory or processor is not the culprit.
Locking seems to be the problem but there is nothing shown in sysprocesses table.
I believe DBCC SQLPERF(WAITSTATS) will be useful to detect lockings but the documentation available is not giving enough information to understand DBCC SQLPERF(WAITSTATS). Can anybody explain how and
what parameter can be useful in understanding locking in this command.
I do use nolock hints in the procedures.....
usuallly transactions are used very minimal and all the processing done on temp database.
Thanks,
Prakash
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
April 30, 2002 at 11:12 am
There is an article in the most recent SQL Server Magazine that briefly mentions it in relation to Page Splits/sec. Basically, the advice is to run it twice and compare the numbers. What specifically are you trying to track with respect to locking?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
April 30, 2002 at 7:38 pm
The only thing is you need to make sure you can do this when nothing else is going on, on the server as you get a squewed look. The values are based on since the server last started or they were cleared. You can do DBCC SQLPERF(WAITSTATS,CLEAR) to set to zero which makes it easier to get values.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 30, 2002 at 9:12 pm
If you run it when activity is going on, compare between two instances. That will give you some indication how the numbers are changing as the system is active. As Antares indicates, if you run when activity is going on, the numbers will be constantly changing. As a result, any comparison is going to be relative.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
April 30, 2002 at 11:52 pm
It's a datawarehouse of bank...
Basically when a single procedure is run it takes some two to three hours while if we run it parallaly with some other processes it get delayed a lot(about 5 to 15 hours). If we kill and rerun the procedure sometime it finished in 2-3 hours otherwise as usual 5-15 hours.
Now as I said processor(8) or memory(4 GB for sql 4 GB for other application) is not the culprit.
Disk I/O is ofcourse a constraint we have 5 raid arrays of each 6 drives. Tempdb is on raid 0+1 while database is on Raid 5. Normally one table size is 4-5 GB(3-9 million rows) I copy all the data in global temp table and then run update and in the end truncate table and drop clustered constraint and insert updated data and create constrained. Earlier instead of truncating table we were updatind the table but this new approach is giving better results.
Now the questions is why when parallel run is creating problem even these processes are using different filegroups(different raid arrays).
To track that I run this command DBCC SQLPERF(WAITSTATS) It shows lot of parameter which is showing lot's of delays, But no book I know is talking about this command comprihensively........
I refer to Inside sql2k, Admin 2000, resource kit 2k, performance tuning technical reference all are microsoft press book. Any other place or book where I can get information about DBCC SQLPERF(WAITSTATS) ??????
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
May 1, 2002 at 6:32 am
Not yet as most of the items have counter parts in Performance monitor and Microsoft is trying to get rid of SQLPERF in favor of Performance Monitor (Even says it in BOL).
quote:
No longer documented. These statements may change in a future release of SQL Server.Use the Windows 2000 System Monitor Windows NT 4.0 Performance Monitor to monitor the performance counters for SQL Server. For more information, see Monitoring with Windows Performance Monitor.
I am researching all the options and will post an article when done.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 1, 2002 at 2:19 pm
There isn't a ton of information available for WAITSTATS, and as Antares has indicated, much of the information is available in other places.
One thing you mention is it is running fine when running alone but when running with other processes it takes a LONG time. I thought maybe blocking was the issue, but you say you can stop and restart and all is well... sometimes. That is very strange. Still, you may want to see what sort of blocking is going on during that process. It may reveal where certain objects are being utilized, causing the issue. Check out the following articles on monitoring blocking:
SQL7:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q251004
SQL 2000:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q271509
Also, check out Performance Monitor and capture the counters. Look at your Processor: % Utilization, Memory: Pages/sec, System: Processor Queue Length, SQL Server:Latches:Average Latch Wait Time, SQL Server:Locks:Average Wait Time. See what kind of difference there is when you run it by itself and when you run it in parallel with other processors.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
May 6, 2002 at 4:30 am
Thanks to all,
I checked with all this stuff this being very helpful.
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply