| Author |
Topic |
|
ssrikrish
Yak Posting Veteran
69 Posts |
 Posted - 2007-12-05 : 11:39:12
|
| Our databases were working fine in terms of performance until over the last 2 months wherein the timeouts and deadlocks have started increasing. We are having growing clients with huge real-time transactions. We have been adding NOLOCKs on queries that are being used frequently etc but the timeouts are still an issue. Not sure what the best way to identify problem areas is. The CPU utilization has also been on the higher side. Profiler identified long queries are being indexed etc. But the performance is still a concern. Any ideas? What am i not looking at? HELP HELP..Thanks,Sri |
|
|
ssrikrish
Yak Posting Veteran
69 Posts |
Posted - 2007-12-05 : 11:46:01
|
| Can overindexing be an issue? |
 |
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-12-05 : 13:58:59
|
| At first you must understand, where is a bottleneck: CPU, Reads, Writes, or Locks. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-05 : 22:33:42
|
| How often do you update statistics or rebuild index? Did you see blocking on the server? |
|
|
|
ssrikrish
Yak Posting Veteran
69 Posts |
Posted - 2007-12-07 : 09:15:18
|
| CPU util has increased.Reads/Writes on certain processes are high.Rebuild indexes happen once a monthUpdate stats happen every 2 weeks. |
|
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2007-12-07 : 10:17:52
|
quote:
Originally posted by ssrikrish
huge real-time transactions.
Are your transactions huge or do you have a lot of concurrent transactions?If you have transactions which involve a lot of rows you may need to look at breaking them up. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-07 : 23:10:53
|
| Should update statistics/rebuild index more often. |
|
|
|
ssrikrish
Yak Posting Veteran
69 Posts |
Posted - 2007-12-10 : 16:17:20
|
| It boils down to blocking. I have one process which updates from a #tmp table. let me paste that query here. This is our main culprit for blocking. can you pls let me know why?This table ABC is a primary transaction table that is being used throughout the system. The below update blocks this table altogether even though PAGLOCK was specified. Even ROWLOCK did not help. It just seems to me that SQL server is always doing a TABLOCK irrespective of the locking hint specified. How do i ensure this ABC table is never blocked? UPDATE B SET IndexStart = TMP.IndexStart, IndexPosition = TMP.IndexStart, IndexStop = TMP.IndexStop, IndexLevel = TMP.IndexLevel FROM ABC AS B WITH (PAGLOCK) INNER JOIN #tmp_IndexBs AS TMP WITH (TABLOCKX) ON B.BKey = TMP.BKey |
|
|
|
ssrikrish
Yak Posting Veteran
69 Posts |
Posted - 2007-12-10 : 16:20:04
|
| More info. The columns involved in the update are indexed. So, i understand the overhead of indexes on an update statement. But i don't know how the amount of blocking we see can be a result due to this. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-10 : 16:21:59
|
I would change PAGLOCK to ROWLOCK for the update, and remove the unnecessary TABLOCK for the temp table.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
ssrikrish
Yak Posting Veteran
69 Posts |
Posted - 2007-12-10 : 17:21:51
|
| Are there any side effects of ROWLOCK? does ROWLOCK make the process take longer to run? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-10 : 17:33:17
|
Maybe fractions of milliseconds, since there could be more locks than pagelocks. It depends on physical storage.But the upside is that the server only locks the records to update, not the complete page where the records to update is stored.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
ssrikrish
Yak Posting Veteran
69 Posts |
Posted - 2007-12-11 : 17:14:25
|
| The ROWLOCK did not help with the blocking. Running out of ideas. Please help. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-11 : 22:22:55
|
| Split the table. |
|
|
|
ssrikrish
Yak Posting Veteran
69 Posts |
Posted - 2007-12-13 : 16:25:42
|
| you mean vertical split? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-13 : 23:14:57
|
| No, should say creating multiple tables with same schema and distributing transactions to those tables. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-12-13 : 23:22:25
|
| how many rows are being affected? divide the update in smaller batcheshave you already looked into the execution plan?is there an index on the temp table?--------------------keeping it simple... |
|
|
|
|
Performance with respect to scalability