Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lou
Yak Posting Veteran
59 Posts |
 Posted - 2002-02-18 : 01:25:23
|
| I have a question about monitoring a high use table. We have a master table which is used by 6+ web sites and a dozen back end applications. We have a recurring problem when a developer writes a new application/Sp with slow running queries. The new application/Sp puts shared locks on the master table and creates havoc on everyone else. The master table isn't dead-locked, the lock is eventually let go. But it is enough to slow down all the other processes and causes a lot of headaches. (I try to tell the other developers to set transaction isolation level read uncommitted if they are just displaying data on the web or updating a temporary display table.)I'm aware of sp_who and some of the system tables. My question is there a better way to monitor this high use Wait Resource and identify the worst offending processes/programs? Thanks. |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-02-18 : 01:30:37
|
| Lou,Fire up "SQL Profiler"...There is enough information there to sink a couple of battleships......DavidMTomorrow is the same day as Today was the day before. |
 |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-18 : 02:16:28
|
| you can use nolock hint too the minimize the locks on your tables.eg:select * from inv_header with (nolock)--------------------------------------------------------------"Happiness is not something you experience, it's something you remember."Edited by - Nazim on 02/18/2002 03:28:35 |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-02-18 : 03:51:28
|
| I'm never keen on not honouring locks in a production system as I've seen it cause completely incorrect data returned.I take it the problem is that these are stopping updates - you probably should redesign the structure o that the updates don't conflict with the retrieval.If you are allowing people who don't know what they are doing to write stored procedures then it should be your responsibility to make sure that everything released is ok.Probably in this case offer some user interface stored procedures and have the clients request you to write others if they have functionality that can't be fulfilled by this. In this way you will also find out what they want to use the database for and if the structure is correct.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
Lou
Yak Posting Veteran
59 Posts |
Posted - 2002-02-18 : 10:30:02
|
| NR,You're right. These Sps are interfering with updates. I didn't design and am not in charge of the datafeeds or anything else. I'm a lowly developer who gets called when things don't work. Usually I have to dig around and either re-write a slow running query or remove the shared locks. |
|
|
|
Lou
Yak Posting Veteran
59 Posts |
Posted - 2002-02-18 : 11:56:59
|
| The web sites are suppose to display "real-time" data. The clients are aware of and expect continuous changes to the data, until the end of the month. At the end of the month, the books are "closed" for that month. The data feed was written by another developer using a App language. The data feed is continuous 24/7. The feed works as long as the other sql processes don't lock the table. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-02-19 : 05:22:33
|
| Say the query locks the table for a minute - this will delay the update. The query gets the correct data from the table but the data in the table is not correct as the update is being blocked.Better to allow the update to go ahead and for the query to get the data as of a minute ago. The next query will get the new data.If the data is being continuously updated then real-time is a dubious concept as the result will depend on how the request gets to the database.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Wait Resource