SQL Server 2000 query performance issue

ButcherOfNog

I'm posting here as a last restort, heh, well somone may have at sometime ran into and got around this one so it might not be in vain .

Ok, using a simple ASP page running on IIS, i fire a series of queries at a SQL 2k DB Server. Even the most complex queries execute and return data in under a second. BUT, on a different SQL Server 2k DB, lets call it "UberDB", the queries take twice as long to execute and return data. The more complex the query the bigger the difference in performance. All the querys are SELECT queries.

The UberDB doesn't seem to be configured any differently, I'm running the exact same DB's on both servers as I've taken copies of the DB's on UberDB and have them running here. Both Servers are running SP3, both have auditing off, I've ran "sp_configure" on the UberServer and have my own configured the same.

The UberDB has no obvious problems, its CPU's are barely ticking over and it has only a small amt of its RAM in use. There are no connectivity problems between the IIS and SQL Server that I can see, plenty of bandwidth available. Pings return in <10ms from IIS to SQL and from SQL to IIS.

regi

Maybe a corrupted index?

DaithiSurfer

Try running your query directly through query analyser on both Servers and see if you have a difference.
If you do then its the server that the problem is on.

Spunj

As DaithiSurfer says, your best bet is probably to start with Query Analyser.

Paste the Query in and on both servers hit Ctrl+L and investigate the results of the Execution plan, should say how much time is being spent on every granular operation.

If thats different then as regi says it could be indexes which may need to be rebuilt among other things. Also make sure you are using the same Connection Library and in the same order on each server - ie if you have both Tcp/Ip and named pipes on the servers.

bonkey

I remember seeing an MS presentation at some point some years ago which was about advanced optimisation techniques for MSSQL.

One interesting thing (at least, I think this is where I heard it) was that different machines can produce differing execution plans for the same query against the same database....the reason being related to certain decisions being made which took available resources into acccount.

So, use Query Analyser with its Query Plan stuff to make sure both queries are running the same.

Another thing to consider is the communication layer. Are both machines being connected to in the same way (i.e. Sockets or named pipes?)

Third thing is the physical location of the DB files - perhaps theyre badly located on the uberDB?

Make sure tempdb is big enough on uberDB too - Thats stung me before.

And finally....the obvious one....make sure there isnt anything dumbass happening on uberDB like an AV program scanning all file reads

Oh...one last thing....

I assume the original DB you are comparing against wasnt actually running on the IIS machine?

jc

bonkey

One more thing has occurred to me.

YOu have - I trust - got both of these machines upgraded to SP3, or at least put the "anti-Slammer" hotfix on each?

(i.e. I hate to suggest it, but could the Slammer virus be the source of your problems?)

Oh - and run a full series of DBCC checks against the master database on uberDB. (use the maintenance wizard)

jc

amen

have you recompile the sp(s) in question on the UberDB machine ?

If the SP accesses TableA which has an Index IDX1 and say the table only has 10 rows and you compiled the sp then it may use a table scan due to small amount of data

if the table now has 10,000 rows and the SP has not been recompiled then the sp will still use the table scan

have a look at
sp_recompile in sql help

or
With Recompile within an SP (although this can cause other issues)

you mention that both servers are running SP3 is this the OS or SQL server version ? Are both OSs running the same version ?

can you post the table, data and sp ?

ButcherOfNog

I'll try a full rebuild of the indexes on UberDB, i have backups of the DB here tho and not having any performance problems at all.

The ASP page i'm running is only a test page, designed to test the speed of getting results from the DB. Its not running SP's, just doing a simple makeconnection and throwing some SQL at the DB and waiting for the results.

IIS (server1) -> query -> DB (server2) -> results -> IIS

IIS and the DB are on different servers

I've ran the queries thru Query Analyser from the IIS box, on the actual UberDB box and from another client PC, same slow performance. Its definately summit with the DB Server and/or Database. I've watched the perfomance of the DB Server while the queries were ran, barely tickles it CPU/Ram wise.

tempdb ... i'll check that out, hadn't thought of that.

As for something dumbass running on the UberDB, I wouldn't be surprised if there was, I would assume that the DBA's in charge of it would sorta have the grey matter to spot this themselves, but now that i think about it .... i'll ask them

The Physical location of the DB files could well be sitting on a Raid array optimised to give crap performance, and if they are theres feck all I can do about it.

Both DB servers are running SQL Server SP3 and Win2k SP3.

sp_recompile .... its just pure SQL, not a SP i'm running.

ta for the replies, i'll let yis know if/when i sort it out.

oh yeah, network libraries etc are the same

Talliesin

Originally posted by amen
With Recompile within an SP (although this can cause other issues)

Goddess yes. Only use With Recompile within a stored procedure if you are absolutely sure it's a good idea, as doing so will mean that it is recompiled every time it's called, so you get non of the pre-compilation advantages of stored procedures.

Using With Recompile when you execute a stored procedure is a different matter, since you aren't forced to do so every time. It can be useful if you expect the call to have unusual characteristics for some reason.

For general maintenance (making sure the optimisations are about right for the table sizes you're dealing with) it's generally better just to call sp_recompile.

ButcherOfNog

Execution Plans of the Query are different. Same Query, same database, same data, different Execution Plans.

At least it shows the other server is running the query different which would possible result in different performance, off to do some research now

DeVore

If the execution plans are different that sounds like a Clue (George, Timmy come quickly we've found a clue!).

The only other thing I could think of was HD defragmentation.... if its severely fragmented that could slow down read times to get the table into memory... thats a long shot though...

DeV.

ButcherOfNog

Getting somewhere now i fink. There was summit up in the indexes and a full reindex of the database later, and we have identical execution plans. Don't know yet if the performance is the same, waiting on results from them on that. Network libraries being run are different version tho so am hunting down their versions and installing them on a box here to run some tests.

Had an interesting morning actually with the execution plans on pre-SP3 SQL Server and a Server with SP3 installed. The query optimizer was obviously overhauled in SP3 as the difference in performance is amazing between the 2. We're talking 7secs before and less that 1 sec after applying SP3. The execution plan pre SP3 is a joke, the query optimizer was creating a temp table and stuffing 5million rows into it, no wonder it was slow