1) FIND THE CULPRITS Like other software’s, we need to understand that MS SQL server is also a computer program but a complex program. Here Microsoft written this complex program. So if there is any problem with the SQL server, we need to understand why this program is not running as we expected. From SQL Server we need to pull and push data as fast & accurate as possible. If we face any issues, reasons may be SQL Server (complex program) needs certain hardware and installation settings which we are not providing properly. The way SQL Server implemented and the way it understands T-SQL code, we are not providing proper T-SQL code to it Even though MS SQL Server is a proprietary software, they provided us a lot of ways to understand the Server and what’s going on inside so that we can use it efficiently. If the SQL server is running without errors, first we need to calculate wait statistics of different threads. SQL server uses threads for every user request. Again a thread is nothing but another program inside complex program which is called SQL server (This thread is not Operating system thread on which SQL server installed. This is related to SQLOS thread which is a pseudo operating system for the SQL Server). We can find wait statistics using “sys.dm_os_wait_stats” DMV. There are many scripts online to query this view as per your needs. I like Paul Randal script(http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts) WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] / 1000.0 […]Continue reading ...
- How to cache stored procedure results using a hash key There are a lot of different design patterns that lend themselves to creating
- SQL Server Database Optimization Guide In the troubleshooting guide we went over the different physical bottlenecks that can
- Yet Another Temp Tables Vs Table Variables Article The debate whether to use temp tables or table variables is an old
- Using Union Instead of OR Sometimes slow queries can be rectified by changing the query around a bit.