Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Hardware or Bad Queries = Performance issues? Expand / Collapse
Author
Message
Posted Friday, December 21, 2012 8:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 24, 2014 6:06 AM
Points: 74, Visits: 267
Help Please!!

How can one identify whether performance issues on a SQL 2008 R2 server are being caused by Hardware or the queries being written inefficently?

I have looked at a lot of DMV data for a server and don't think particular issues are because of hardware. However what should I look at that would point me in the correct direction and can tackle the supplier to resolve the issues with their application?

Any advice on the above will be highly appreciated.

Thanking you for your help
Kailash.
Post #1399441
Posted Friday, December 21, 2012 8:31 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 3,998, Visits: 7,172
What kind of hardware do you have running?

If you suspect bad queries, look for ones that have especially high reads/long durations, as these may be potentially missing indexes (insufficient indexes), check the server/query waits to see which are the highest. For instance, high PAGEIOLATCH_EX "may" indicate issues with the disk subsystem (hardware) because it's waiting to look up the page from the disk, CXPACKETS may indicate poorly written queries (or ones that have large table scans in them), and so forth.

This is such a loaded question, what specifically have you looked at?

FYI - if you're confident that your indexes are solid and your statistics are up-to-date, then most often you can dig deeper into the hardware configuration (RAM, spindles, etc)


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1399450
Posted Friday, December 21, 2012 9:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:17 AM
Points: 40,450, Visits: 36,905
When in doubt, assume bad queries and poor indexing.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1399484
Posted Friday, December 21, 2012 9:24 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 3,998, Visits: 7,172
@Gail +1,000,000 - that's 99% the case here at our shop

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1399487
Posted Friday, December 21, 2012 9:26 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:17 AM
Points: 40,450, Visits: 36,905
MyDoggieJessie (12/21/2012)
For instance, high PAGEIOLATCH_EX "may" indicate issues with the disk subsystem (hardware) because it's waiting to look up the page from the disk, CXPACKETS may indicate poorly written queries (or ones that have large table scans in them), and so forth.


On the other hand, PageIOLatch waits may indicate queries that are requesting far too much data (table or index scans) and CXPacket may indicate that you have lots of queries benefiting from parallelism



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1399489
Posted Friday, December 21, 2012 12:06 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 24, 2014 6:14 AM
Points: 65, Visits: 404
GilaMonster (12/21/2012)
MyDoggieJessie (12/21/2012)
For instance, high PAGEIOLATCH_EX "may" indicate issues with the disk subsystem (hardware) because it's waiting to look up the page from the disk, CXPACKETS may indicate poorly written queries (or ones that have large table scans in them), and so forth.


On the other hand, PageIOLatch waits may indicate queries that are requesting far too much data (table or index scans) and CXPacket may indicate that you have lots of queries benefiting from parallelism


LOL, so those waits really don't tell a person much!

I've found some queries that tell me what's running when an issue arises. I also check the standard reports for top queries in regards to CPU and IO. Then you can try to work on those queries.

Does anyone else do anything other than that to find issues on servers if you're not even sure issues exist?
Post #1399546
Posted Friday, December 21, 2012 12:23 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:17 AM
Points: 40,450, Visits: 36,905
scogeb (12/21/2012)
LOL, so those waits really don't tell a person much!


Well excessive PageIOLatch indicates that the IO subsystem is been driven harder than it can handle. Whether that's because too much is being asked or because the hardware is inadequate requires further investigation.

CXPacket just indicates that queries are running in parallel. It's a wait a lot of people misinterpret.

Try http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/, start with chapter 1.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1399550
Posted Friday, December 21, 2012 1:03 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 3,998, Visits: 7,172
Taking this with a grain of salt and the "it depends" answer, another way to see if you have disk contention is to track the average disk queue length for the disks - this can often tell you if the bottleneck is occurring within your disk subsystem (of course as Gail has already mentioned, always assume bad indexes/stats).

I've seen disk queuing become excessive when a query was performing hundreds (sometimes millions) of reads on a table due to an clustered index scan on a table with 30+ million rows. A little magical index tweaking later and the result was a 100% non-clustered index seek, and the reads went down to < 100K, and the disk queuing went significantly down.

Again, without knowing what your hardware architecture is, it's difficult to really help you out. Our suggestions could be night/day difference on a server with 4 spindles in a RAID 5 versus a server with a SAN containing 64 spindles. Performance in these cases (i.e. like where you should be concentrating your efforts), is relative.


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1399562
Posted Friday, December 21, 2012 1:14 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:17 AM
Points: 40,450, Visits: 36,905
MyDoggieJessie (12/21/2012)
Taking this with a grain of salt and the "it depends" answer, another way to see if you have disk contention is to track the average disk queue length for the disks


Sorry to pick on you today, but disk queue length is a near-meaningless counter these days. There's too much between the server and the disks to get a sensible interpretation of that counter (unless you're dealing with direct attached drives), plus SQL is designed to intentionally queue up multiple IOs, thus sending the queue length very high (read ahead reads).

The avg sec/read and avg sec/write are a lot easier to interpret and track



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1399571
Posted Friday, December 21, 2012 1:18 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 3,998, Visits: 7,172
It's all good I appreciate constructive criticism(s)

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1399572
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse