simple-talk - SQL Server Performance-The Crib Sheet
Contents
- Introduction
- Overview
- Measuring Performance
- Perfmon
- Perfmon Counter Set
- Using perfmon
- Evaluating perfmon data
- Server health
- Memory
- Memory Grants Pending
- Lock Requests/Sec
- Deadlock/Sec
- Extended Events
- Dynamic Management Objects
- Third Party Tools
- Tuning Performance
- Server Performance
- Database Performance
- Indexing
- Files and FileGroups
- Normalization
- Data Types
- Other Issues
- T-SQL Performance
- Client Access
- Testing Performance
- Suggested Reading
...
How much performance is enough? Where do you start tuning? When do you stop tuning? Each application being developed will answer these questions in a different way. The important thing is not to establish a single mechanism for answering them. Your goal is to establish best practices and guidelines that will lead to the answers in the right way for the application under consideration.
First, and most important, the SQL Server system itself needs to be configured correctly. It also needs to be running on a correctly configured Windows server. This is the foundation on which the databases will be built. These same requirements do not exist when dealing with an Azure SQL Database. After the server is configured, you need to design and build the database to perform optimally. That's assuming you're building a new database. If you're trying to tune an inherited database, then you'll want to know what a good database looks like. Appropriately designing the database is even more important in Azure. Once the server and database are out of the way, you need to be concerned with the code running against it. This means the views, triggers, functions and, on local copies of SQL Server, the CLR code. It doesn't stop there because you need to be sure that the development staff is accessing the database correctly either in their general use of the database and it's code, or in their own use of ADO or whatever other client they might be using to access the system
In order to address all these concerns you need to understand how to measure and test performance. Once you've measured the performance and found it wanting, you'll need to know what to do about it. After addressing structural changes to the database or changes to the server or refactoring the T-SQL code, you'll need to have a mechanism in place to test the results in order to be sure your work is accurate
After all this, you should have a correctly functioning system that performs and scales well.
...
..."
Should be enough to get you stared at least in looking into your DB performance "issues".
0 comments:
Post a Comment