Gail Shaw, famous for her forum contributions under the pen name 'gilamonster', is from Johannesburg Gauteng and works as a database consultant in performance tuning and database optimisation. Before moving to consulting she worked at a large South African investment bank and was responsible for the performance of the major systems there.
Gail Shaw examines three common forms of generic SQL that can and will confuse the SQL Server Optimizer to the point that it generates and reuses very inefficient execution plans. Want to 'trick' SQL Server into performing millions of logical reads to return the data, when only are few thousand were really required? Try writing catch-all queries, or using control flow statements to create general-purpose procedures, or switching parameter values within a procedure. Just don't expect the resulting queries and procedures to perform well, or consistently. The ideal solution to the problem of generic T-SQL is not to write it, but failing that Gail demonstrates techniques such as recompiling the query on each execution, using hints, dynamic SQL, or splitting generic procedures into sub-procedures.… Read more
When a SQL Server instance deadlocks, it can be anything from minor irritation to something far more severe. In this article, Gail Shaw looks at how you can identify common types of deadlock, the difference between a deadlock and severe blocking, and how to avoid and fix the most common deadlock types.… Read more
Everyone's SQL Server nightmare: The instance will not start. If such a problem strikes you, keep calm, follow Gail's advice, and you'll soon be back up and running. In the meantime, practice these steps so as to be ready! … Read more
For the latest in our series of SQL Server Howlers, we asked Gail Shaw which common SQL Server mistakes and misunderstandings lead to tearful DBAs and plaintive cries for help on the forums.… Read more
In the first part of this series of articles, Gail showed how to find the procedures that contributed most to performance problems. In this final part, she shows how to use query statistics to pinpoint the queries that are causing the biggest problems, and then use the query execution plans to find out which operations are the root cause. With this information one can select and modify the indexes that can optimize the plans and therefore performance of the query.… Read more
To tackle performance problems with applications, you first find the queries that constitute a typical workload, using SQL Profiler: Then, from the trace, you find the queries or stored procedures that are having the most impact. After that, it is down to examining the execution plans and query statistics to identify queries that need tuning and indexes that need creating. You then See what effects you've had and maybe repeat the process. Gail explains all, in a two-part article.… Read more