| Author |
Topic |
|
catparks
Starting Member
18 Posts |
 Posted - 2004-01-27 : 09:05:08
|
I'm not sure if I am posting this question in the correct forum...but I am having a periodic problem with locks on a database. The database is SQL Server 2000, and it is the database to an application we purchased. A BIG application. What is happening is periodically a user will run a query that causes a lock on the database for 10-15 minutes. (Enough time for the help desk to get flooded with calls because 'the app. stopped working'.) Anyway, I called Microsoft to see if they can help me track down either the sql statement running or the user (I.P. address). They want me to run a diagnostic tool constantly, which is a little concerning on a production server.Before I run the diagnostic tool, I would like to get an idea of how often locks are happening, and for how long. Does anyone know how I can monitor this without constantly watching the server?Thanks! |
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-01-27 : 09:26:17
|
| If the diagnostic tool you are referring to is the SQL Profiler, I'd go with that. The alternatives of viewing activity in Enterprise Manager, or querying the master tables such as syslocks and sysprocess are a lot less satisfactory.You're right to worry about running diagnoistic tools on production systems, can't you restore the database to a development system and simulate the user activity there giving you the profile info you are after?Raymond |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-01-27 : 09:56:11
|
| Tryhttp://www.nigelrivett.net/sp_nrSpidByStatus.htmlIt will show what is running and blocking and the commands that are causing it.There are some other things on there which might be helpfull too.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-27 : 12:38:36
|
| It's peoplesoft, isn't it....When you say people run queries....is this adhoc coders?It's not because of the interface is it?Brett8-)EDIT: And if it is the vendors code...I'd call the vendor....that's unacceptable.....you must have a site support agreement...make'em fix it... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-27 : 12:43:35
|
| Besides SQL Profiler, you should be running Performance Monitor to determine if the reason for the blocking is due to hardware bottlenecks. SQL Profiler will help you diagnose which queries are problematic. Once you know that, you can determine if adding or removing indexes will help you out. But Performance Monitor will let you know if you are having problems at the hardware level. Is your server above 80% for CPU at all times? How much memory do you have available? What is the buffer cache hit ratio? How about context switches/sec? These questions can be answered with Performance Monitor. For SQL Server performance information, check out sql-server-performance.com.Tara |
|
|
|
skyfire1
Starting Member
4 Posts |
Posted - 2004-01-30 : 11:56:43
|
| X002548 it's interesting that you mention PeopleSoft. Most of my locks are caused by people running adhock queries in PeopleSoft. Do you know any remedies? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-31 : 10:16:35
|
Stop letting them run ad-hoc queries.  You might want to ask users what kind of queries they run most often, and set up views or stored procedures to deliver them. This allows you to optimize them to have less of an impact on resources. The only other alternative is to set up a separate server for reporting purposes, which is of course pretty expensive. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-01-31 : 11:13:51
|
| Microsoft is probably recommending that you run PSSDIAG. This tool generates perfmon, profiler and blocking script output. Very useful for troubleshooting purposes.I would suggest you run it until you can capture a couple of your slowdowns, and then turn it off. You should also take Rob's advice and not let end users run queries against your OLTP system.-ec |
|
|
|
|
SQL Server Locks