I always get a kick out of when a query needs to perform a look up on the last 4 digits of a column because it usually ends up being something radioactive like a credit card number or a social security number. Either way, trying to do a lookup by the RIGHT most characters of […]
Read MoreSQL Server
Don’t use UPPER in WHERE unless you really need it
In SQL Server when I run into a problematic query doing an UPPER on a column in the predicate (WHERE clause), I check the collation scheme of the database. If you have a Case Insensitive (CI) collation scheme you don’t need to use UPPER. As with most functions applied to a column, it is unsargable […]
Read MoreHow to see what is in your buffer cache
If you have ever wondered what database or tables/indexes were consuming the most memory/buffer in your database, this post can help with that. How do I see this? The key DMV’s to view this are sys.dm_os_buffer_descriptors, sys.allocation_units, and sys.partitions. The key piece to identifying what is currently in the buffer cache is sys.dm_os_buffer_descriptors. The use […]
Read MoreTempdb caching – What is it and how to take advantage of it
There is overhead associated with creating objects in tempdb. Sometimes this overhead surfaces as pagelatch contention – most commonly in PFS, GAM, or SGAM pages. Tuning to alleviate PFS, GAM, or SGAM contention isn’t so much the topic I’ll be writing about; however, this will help alleviate the pressure on tempdb. What is tempdb caching? […]
Read MoreWhat happens with parameterized wildcards?
I’ve been curious of how the optimizer behaves when using parameterized SQL with a LIKE clause. Wildcard values that have a wildcard character in front generally do not seek on indexes. I was curious how the optimizer deals with this from a cached plan perspective – would placement of the wildcard character cause recompilations? I […]
Read MoreHow STRING_SPLIT row estimates can affect performance
Recently someone brought a tweet to my attention regarding STRING_SPLIT and how performance of it was faster than table valued parameters (TVP). As you may have read in one of my prior posts TVP’s have issues related to lack of good row estimates. This can be fixed with a little extra work. STRING_SPLIT caught my […]
Read MoreWhy do I have multiple plans for one query?
Have you ever been digging in your plan cache with either sys.dm_exec_query_stats or sys.dm_exec_procedure_stats and seen two identical queries with different execution plans? When I say identical I’m referring to having the same sql_handle. A sql_handle is generated based on the query text submitted to sql. If one character is different (even white space) it […]
Read MoreHow to find your busiest database from sys.dm_exec_query_stats
Earlier today I read a blog post detailing how to find your busiest database by querying sys.dm_exec_query_stats. The blog post suggested you use the dbid column from sys.dm_exec_sql_text. There is a problem with this. The dbid is NULL for anything that is not a stored procedure. This is what was suggested in the blog post […]
Read MoreIs your system_health session missing data?
It is fairly common knowledge that the system_health session is a useful tool for resolving issues such as deadlocks. I see a lot of posts on other websites where people report a discrepancy in the number of deadlocks monitored by perfmon or tracing vs what shows up in the system health session. This is the result […]
Read MoreTuning 3rd party software packages? Yes, it is possible.
You didn’t write it, you can’t modify it, but you’ve been asked to support it. How do you deal with this type of challenge? I love a good performance challenge and the constraints on this one always make these fun to solve. I’ve been in many consulting scenarios where a client implemented a software package, […]
Read More