SQL Server Performance Forum – Threads Archive
Poor SQL performance no obvious bottle necks
I have a tricky one for those willing…….. I have a dual xeon with HT and a raid 5 configuration everything except one aspect of the database (scanning and updating a 500,000 row table) runs nice and fast. The table in question is well indexed and the speed problem seems to be in the update not the scan (based on Query Anlysis) however none of the Physical Disk perfmons are showing unusual numbers for the configuration indicating a IO bottle neck and the CPU is no where near maxing and ram is not fully utlized… There is no deadlocks being reported the only suspect thing I can locate is that according to DBCC SQLPerf(waitstats) SLEEP, RESOURCE_QUEUE and WRITELOG are the waittypes were all the time is going. I assume it is the RAID but before going to 0 or 1 I want to confirm this…. any advice for where to find the problem would be appreciated…try set statistics io on to see how many pages are read.
it is an appropriately indexed query?
check that the table is not fragmented
any locking/blocking issues? raid 5 is faster than raid 1 for writing, and raid 0 seems like a BIG backward step
(oops yes Joe’s right, that should be RAID5 better than RAID1 for reading) you do have the physical counters actually turned on, i.e they do move off 0? (apologies if you’re offended but I have to ask…) Cheers
Twan If none of this helps, could you post the query, table definition and indexes?
raid 5 is the worst possible disk config for random writes, depending on your table and update, you could be doing either random or sequential writes.
how is the data and log files distributed across each disk set?
also, try OPTION (MAXDOP 1) on the update
Okay I have taken your tips and turned the statistics io and can see that the select (record locator) part of the SQL is performing 125 read, and the update is performing 6, so I am now leaning towards thinking the time is taken up in the select part.. I also checked the index and table fragmentation and all the indexes and tables in question have a scan density close to 100% ( I have a DB maintenace plan running weekly). I also checked the drive fragmentation and it was awful so it has been defraged. Another strange behaviour, if I take the select in question that is generating the 125 reads and run it byitself in Query Analyser is only does 6 reads, and if I compare the execution plans of the select in Query Analyser to the one in the stored proc when executed via query analysier one takes up 80% the other takes up 4%….. So it appears the problem is that the stored proc version is doing something to make the select read more, and thus take longer.. Any ideas ?
Sorry I also meant to cover off your other suggestions: OPTION MAXDOP did not effect, and yes the disk perf counters are on (no offense taken) as I can get them to skyrocket by doing a 100,000 row insert… The log files are on a separate aray (also raid 5). I just did another test and copyed the entire stored proc into QA and executed.. in QA it only does 6 reads!
Sorry, final post about this… I have been doing more testing and can get the 125 and 6 reads to appear in QA… all of these reads are logical, the query which gives me six: Select ID
From Table WITH (NOLOCK)
Where Table_UID = 1 and Table_email = ‘[email protected]‘ The query which gives off 125 is the same except instead of using the email hardcoded I have it in a variable (as it is in the stored PROC as it gets passed in) Select ID
From Table WITH (NOLOCK)
Where Table_UID = 1 and Table_email = @stEmail The table has a non clustered index on Table_UIDand Table_email, I am really lost as to why when using a variable the ready rate skyrockets..
Hi Mike, How many different values are there for Table_UID? I’d suspect that you need to change the order of the columns in the index… ( Table_email, Table_UID ) The difference between the literal and the variable is that SQL uses a magic number to work out whether the query can use an index when a variable is used… You should see a change in the execution plan between the literal and the variable queries Cheers
Twan PS logfiles are much better on RAID 1 or RAID 10…
what are the data types for Table_email, and Table_UID?
ex. if Table_UID is smallint and you write Table_UID = 1 in the WHERE clause, SQL will convert Table_UID to int. if Table_email is nvarchar and your variable is varchar, that might be a issue as well
please provide more info on this difference in plan between literal and variable.
]]>