Sql Server Performance Tuning
Performance Tuning is a key activity at the heart of DBA and devops activities. Sometimes it can be incredibly challenging, turning slow T-SQL and inefficient schema and index design into a well optimized database server that will keep applications running quickly long into the future. MiniDBA is designed to make the process quick and simple, saving you potentially huge amounts of time.
Successful performance tuning requires a lot of information to be made available to you such as your slowest T-SQL operators, what files share disks, how many cpu cores are being used by each query, requested memory by session to name a few. Individual T-SQL queries run from management studio will get you most of these, Perfmon or WMI counters will get the rest. Once you have those details you have a mostly static picture of what is happening in your SQL Server. MiniDBA keeps constantly refreshing this picture allowing you to see changes to performance you make in real time at the highest time resolution on the market (2 seconds).
The following sections detail the functionality offered by miniDBA to help you effectively performance tune not just single queries but entire SQL Server estates.
Execution Plan Analysis
- All execution plans displayed by either slow overall time, high CPU or high IO/disk useage
- SQL execution plans broken down by expensive operators - see green bars in Screenshot
- No more tracking back 'thick lines' in graphical execution plans (graphical plans are available in the app)
- Don't spend time working backwards down large graphical execution trees, get straight to the offending slow operator
- MiniDBA shows you what the SQL Server query optimizer is doing
Health Checks
- T-SQL is analysed for inefficient code
- Database and server level specific code is reported upon for slow performance
- In a few seconds miniDBA will tell you about low level performance problems an expensive consultant would take days to report on
- Server configuration issues are identified that will have a negative effect on performance
- MiniDBA offers the "About" section for every health check, which offers background information and advice where possible
- Links to helpful articles such as MSDN and Stackoverflow
Index Analysis
- A huge amount of sql performance tuning comes down to indexes that aren't working quite right
- Index Fragmentation is highlighted with unique heatmap of problems
- Unused and necessary indexes are listed in every database
- Real-time index useage lets you watch what indexes are getting used as your query executes instead of what was expected to be used
- Use miniDBA's index analysis and defragmentation jobs to keep fragmentation low where it counts
Real Time Wait Statistics
- miniDBA uses the Microsoft endorsed waits and queues method to identify what you code is waiting for
- Watch live as your code executes and see what wait types occur
- Every microsecond you wait for your code to execute there is a reason for it - know what the reason is
- miniDBA gives an explanation of each known wait type
- MiniDBA can show you either total, current or accumulating (reset the waits to zero in the app and watch them increase) wait stats
MiniDBA removes the mystery of the SQL Server 'Black Box' - the internal workings are now exposed to you without having to write DMV meta table based queries that take time to research and test.