We have been doing DBA work for long, if you have been a DBA for a number of years you know that performance tuning SQL Server is not an exact science. To do a pretty thorough audit we have put together a quick checklist.
To make your SQL Server performance audit easier, we have [...]
Posts under ‘sql server performance’
How to Conduct a SQL Server Performance Audit
Performance Intelligence
Performance Intelligence is technology that uses historical trend data to resolve current database performance problems. We have been testing a free download of Ignite 8 from Confio Software. The marketing propaganda says that Ignite uses Performance Intelligence to analyze millions of data points, captured in the Data Warehouse along multiple dimensions, and identify which issues [...]
Troubleshooting Performance Problems in SQL Server 2008
Troubleshooting Performance Problems in SQL Server 2008 Whitepaper
This SQL Server white paper gives you step-by-step guidelines for diagnosing and troubleshooting common performance problems by using the following tools:
▪ SQL Server Profiler
▪ System Monitor (in the Windows Server 2003 operating system) or
▪ Performance Monitor (in Windows Vista operating system and Windows Server 2008), known as Perfmon
▪ [...]
Analyzing the SQL Diagram
Analyzing the SQL Diagram begins by looking for the smallest, underlined number. In our case it
is 0.002 next to the CLASS table. To limit the number of rows the query needs to process,
starting here will trim our result sets the soonest. In other words, if we can make SQL Server
start with this table the query [...]
Creating a SQL Diagram
We would like to introduce a technique that we use to tune SQL statements correctly without the trial and error that we were often faced with before. The concept of SQL Diagramming was introduced to us through a book named SQL Diagramming by Dan Tow. For a complete understanding of this topic we encourage you [...]
Gathering SQL Statement Metrics
The next phase in getting better performance out of your sql server application and to tune sql statements is to gather critical information and metrics about the SQL statement. These metrics should include the following:
How long does the statement take now?
What is acceptable to the end users? If they want the query to return [...]
Identify Database Wait Time View
Part – 3 in “How to identify which sql statements to tune” involves identifying database wait time view. Wait time information is very critical to successfully tune SQL statements. When SQL statements execute, SQL Server has instrumented their code to give information about where it is spending time. If a statement runs for 3 minutes, [...]
How to know which sql statement to tune
Part two of “How to know which sql statement to tune”. When we mention End-to-End view, you may think of the performance of the application from the web browser or client application, through the application server and to the database. That is technically correct, but from a business perspective, you should also know the SQL and [...]
SQL Tuning Process
Working with many other customers, we have developed a sql tuning process that works very well for us. This does not mean it will work for you as is, but it’s a good starting point for anyone. The process centers around four main steps:
1. Identify – pick the correct SQL statement to tune and avoid [...]
Critical Resource Data Collection Issue
Some versions of SQL Server 2000 and 2005 have bugs that cause memory leaks that can, over time, cause SQL Server to run too low on memory and eventually crash. The Ignite PI 8.0.27 Beta release collects data on some system resources by calling stored procedures (that have the aforementioned bugs) on the SQL Server [...]
