SQL Server Performance Forum – Threads Archive
2 Databases, Different run times…
I have 2 equally structured databases under the same Sql2000 Server with exact table structures & stored procedures. The main difference is that 1st Db is at size of 2736MB & 2nd Db at size of 3471MB.A similiar stored procedure executed produces drastically different times.
1st DB at 30 secs, 2nd DB at 358s..
[This stored procedure packs a series of processes to insert new rows, does updates & deletes across about 5-8 tables..] I have done indexes for all the tables, though I might have overdone it.
[May have more indexes than really needed.]
Still the 1st Db can do it in 30s.. I have around 402383 entries for about 5 tables in the 1st Db….
and around 52,2142 entries for about 5 tables in the 2nd Db…. Not sure if I need to further optimize my transact-sqls,
[have tried so already, but the diff in the runtime is unusually big,
to cause concern and I was unable to figure out why… ],
re-do my indexes,
or break the 2nd DB up into distributed Dbs..[or all of these]
Please advise…. Thanks. Am still limited in my tuning experience to transact-sqls & indexes..
could be different disk config
but my guess is different execution plan due to different data distribution
I have the same problem with the same database schema on LocalDBServer and QADBServer. The hardware configurations for both servers are slightly different, with LocalDBServer being dual core, and having an extra gig of RAM. But the storedproc on LocalDBServer runs in 30 seconds with the QADBServer running in slightly over 3 minutes. I’ve updated statistics on the relevant indexes to no avail. I thought that since we run load tests on QADBServer, the temp db might be extraordinarily large, so I restarted SQL Server. What is unusual is that the Execution Plans of the same stored procedure are different between the two servers. One uses a clustered index scan on Table1 and the other uses IX_for_storedproc on Table1. Table1 has the same number of records between the two. Why this difference in execution plans? Is this key to the difference in run times?
Was unable to return to the forum for last few days,
tried to rewrite all of my stored procedures
[key is reduced all unneccessary updates/deletes,
cuz I was previously calling out to a another-procedure to process a sequence,
now I customized the call to only the tables that are specifically affected..
and tried encapsulating more procedures as I could…]
and also trimmed down all my indexes to the minimal used…
.. I tried implementing this to the server,
and re-started the server…
I got immensely better runtimes… the 2nd DB down to 26s..
and the 1st DB at about 16s. I was thinking, it maybe the excessive indexes that was the problem..
especially with loads of adding/deleting in my procedures,
it was hurting my heavier 2nd DB…. This is all I can conclude for now….
Thanks for all your replies, appreciate your thoughts…
]]>