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 |
|
ssrikrish
Yak Posting Veteran
69 Posts |
 Posted - 2007-09-16 : 12:22:17
|
| hi,We have a SQL 2005 transaction database server that suddenly started to issue deadlock errors last week on most of the databases on that server and a lot of timeout errors. Before that, that database server performed very well and timeouts were minimal to zero. I am not sure what changed for it to have these performance problems. The only major change we did was to convert several varchar columns to nvarchar in several tables (as part of internationalization initiatives). We did not modify the procs from varchar to nvarchar though but would be doing that phase by phase.There is also one proc in which we used the snapshot isolation level of sql server 2005. These are only 2 major changes done within the past 2 weeks. Would these be the cause for these deadlocks and timeouts on our web-based application?Any ideas?ThxSri |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-16 : 13:00:47
|
| Might require recompile of the SProcs involved, if that did not happen when you made the change. A stop/start of SQL Server, or clear the cache (which is a bit drastic for any other application running on that server) would have the same effect.There may be an issue in JOINs when comparing columns of different datatypes when an implicit cast occurs - e.g. a varchar with an nvarchar column.Kristen |
 |
|
|
ssrikrish
Yak Posting Veteran
69 Posts |
Posted - 2007-09-16 : 15:31:15
|
| We usually never do joins on char fields.It is always based on int fields.-Sri |
|
|
|
ssrikrish
Yak Posting Veteran
69 Posts |
Posted - 2007-09-16 : 15:33:14
|
| Also, kristen. This is a dedicated server just for sql 2005. No other apps run on it.i did notice that the CPU utilization has been shooting up and down frequently. Average is 50%. - Sri |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-16 : 17:44:44
|
| How often do you rebild index or update statistics? You can find out offend sp in sql server log then trun them. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-17 : 04:06:29
|
"convert several varchar columns to nvarchar in several tables"Additional thought:That will double the amount of storage required, which in turn will half the number of records/page (and index entries), and increase the network transfer time."half the rows per page" assuming all columns are now nvarchar, and of course many will be INT etc  But as rmiao says, use SQL Profiler to find "slow running queries" and have an in-depth look at the worst culprits.Kristen |
|
|
|
ssrikrish
Yak Posting Veteran
69 Posts |
Posted - 2007-09-17 : 23:30:11
|
| hi,Thanks for your suggestion. I figured out the problem. Apparently, someone has rolled out a new table last week that did not go through proper channel for approval and that table was not indexed and considering its size, it was doing a table scan and caused timeouts. I have indexed the table properly and no deadlocks caused since the index was rolled out.- Sri |
|
|
|
|
|
|
|
|
deadlocks