1 / 23

SQL Server Performance Tuning

SQL Server Performance Tuning. ABOUT ME. Aditya Badramraju Twitter: http://twitter.com/@aditya_feb22 Blog: http://adityabadramraju.wordpress.com. Agenda. SQL Server Performance Tuning Overview CPU Input/output Memory Conclusion. Slow Performance !!! . U DBA!!

Anita
Download Presentation

SQL Server Performance Tuning

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server Performance Tuning

  2. ABOUT ME AdityaBadramraju Twitter: http://twitter.com/@aditya_feb22 Blog: http://adityabadramraju.wordpress.com

  3. Agenda • SQL Server Performance Tuning Overview • CPU • Input/output • Memory • Conclusion

  4. Slow Performance !!! U DBA!! I want application run in rocket speed

  5. So How Do I Do

  6. Monitoring

  7. Troubleshoot/Get the victim

  8. Tune

  9. Test and Implement

  10. Get the victim!! The victim for performance majorly lies in any of these areas • CPU • IO • Memory • Temp DB Bottle Necks • Poor Indexing ,query writing and maintenance

  11. CPU • Check the Task Manager • Check Perfmon Counters • If SQL Server is responsible for high CPU find the session which is consuming the CPU • Dig in to details of the sessions • Find the CPU costing queries • If the environment is OLTP beware of MAXDOP

  12. IO • DMV sys.dm_os_wait_stats is the best thing which says you are at IO crunch • Perfmon counters such as Diskreads/Sec, Diskwrites/Sec, IO queue also gives clear picture in this front. • When you found IO as culprit • Spread the data into different drives • Check the queries which costs high Logical IO • Check for DISK Defragmentation and Index Fragmentation

  13. Memory • We can get the very first glance of it through Task Manager • Perfmon Counters like Page Life Expectancy, Buffer Cache Hit Ratio, Memory Grants Pending, Free Pages • Page file configuration on the server • Adhoc Queries • Poor Query Writing • Incorrect Memory Settings • Lock of Memory Pages in 32 Bit System.

  14. TEMPDB Bottle Necks • Its used more than What we normally think • It is responsible for all Sorts, Hashes, DBCC, Index Rebuilds and more. • The bottle necks of TEMPDB are due to • Incorrect tempdb configuration • Incorrect usage of temporary variables • Unnecessary Sorts on major tables etc.

  15. Poor Indexing and maintenance • The above said problems are inter related and basic problems will be • Poor Query writing like using joins(Not appropriately of course ),Cursors, triggers etc. • Having huge index fragmentation and huge page density • No update for statistics • And lastly not having a Dedicated DBA (ATLAST I HAVE SAID THAT )!!!!

  16. Acknowledgements • Paul Randal –SQL SKILLS • Adam Machanic-Microsoft • SQL Server Internals by Kalen • Pinal Dave –SQL Authority • SQL PASS and all SQL Organizations

  17. LAST WORD I like to MAD and U??? ……

More Related