Types of SQL Server Indexes
In this tip we will go through each type of index that is available in SQL Server and give an overview as well as advice on which index types fit each particular use case.
2020-01-21
Query performance
Optimize Your SQL by Reformulating the Spec
As SQL developers, we tend to think of performance tuning in terms of crafting the best table indices, avoiding scalar and table valued functions, and analyzing query plans (among other things). But sometimes going back to the spec and applying some properties of elementary math can be the best way to begin to improve performance of SQL queries which implement mathematical formulas. This article is a case study of how I used this technique to optimize my SQL implementation of the Inverse Simpson Index.
2021-05-07 (first published: 2019-09-12)
5,284 reads
Index Key Column Order And Locking
Sometimes, simply switching the order of columns in an index means SQL Server takes more fine-grained locks, for a shorter period of time, causing less blocking.
2019-05-31
Deadlock Graph Frustrations
No matter what you use to collect or display them, deadlock graphs can return some confusing information.
2019-05-31
User-defined scalar functions suck – even when they don’t access data.
Even if your scalar function doesn’t touch tables, it still cripples performance by forcing serial processing, blowing up your CPUs, and obfuscating your query plans. Scalar user-defined functions: not even once.
2019-05-31
Are Self Joins Ever Better Than Key Lookups?
Erik Darling explains a trick that has a specific use case, but can be quite effective when you spot it.
2019-05-31
Minimal Logging with INSERT…SELECT and Fast Load Context
This post provides new information about the preconditions for minimally logged bulk load when using INSERT...SELECT into indexed tables.
2019-05-31
What Parameter Sniffing Looks Like In The Plan Cache
If someone sends you a cached plan that’s slow, how can you tell if it’s because of parameter sniffing?
2019-05-31
Index fragmentation, part 2
Does index fragmentation hurt query performance? Possibly. It depends on your workload. Tibor Karaszi explains.
2019-05-31
12 Ways To Rewrite SQL Queries for Better Performance
Bert Wagner summarizes 12 techniques he's learned over time, from trial and error, for rewriting queries to improve performance.
2019-05-31
Blogs
New Job -> Week 2 -> Creating a Checklist
This is part of a series of new job blog posts. You can find...
SQL Server 2022 Administration Inside Out
By Will Assaf
SQL Server 2022 Administration Inside Out is now available for pre-order from Microsoft Press. This...
WIT/DEI Mental Health and Wellness Day Challenge to Present
Back in December 2019, I started presenting my session on mental health with lots...
Forums
Get a Date Time Stamp of table when another columns value changes.
I am having Below Table i want to get Result as shown in the...
how to write a query by using some filter patterns in SQL Server?
what I want is to apply some filter patterns to search the data. For...
Tsql query to show currently running sql agent jobs
Hi All, Using Tsql query I want to know currently running sql agent jobs...
Ask SSC