An index can reduce the Performance of SELECT Queries when it changes a particular plan unfavorably. Readers asked three follow-up questions.
Why create the index in the video?
My demonstration showed one unfavorable plan change. It did not establish that every index is a distraction. Indexes remain central to SQL Server performance.
What else do I check?
My broader health check covers configuration, I/O latency, files, tempdb, Windows settings, waits, error logs and maintenance. Those categories remain useful. The historical check counts were not a universal technical standard.
How do I assess unused or missing indexes?
The linked DMV scripts have collection intervals and reset limits. A lightly read index may enforce a constraint or support occasional critical work. Missing-index suggestions also require review.
Compare plans, reads, CPU and duration using representative parameters. Include write and maintenance costs. Preserve a rollback definition before removal. The video shows the original specific example.
Related reading
Original video
An index demonstration is not a universal index policy, it is evidence for a specific workload and plan.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
5 Comments. Leave new
Another great way to improve query performance is to write a better query…
Server Level Configuration Check (8 Point Checks)
Database Level Configuration Check (6 Point Checks)
I/O Distribution and Latency Analysis (4 Point Checks)
Data and Log File Inspections (3 Point Checks)
TempDB Analysis and Review (2 Point Checks)
Windows Configuration Check (3 Point Checks)
DBCC Best Practices (2 Point Checks)
SQL Server Wait Statistics Analysis and Strategy (5 Point Checks)
Error Log Review (3 Point Checks)
Miscellaneous Performance Checks (3 Point Checks)
Where I can find Checks Points which is noted in below points in brackets.
Server Level Configuration Check (8 Point Checks)
Database Level Configuration Check (6 Point Checks)
I/O Distribution and Latency Analysis (4 Point Checks)
Data and Log File Inspections (3 Point Checks)
TempDB Analysis and Review (2 Point Checks)
Windows Configuration Check (3 Point Checks)
DBCC Best Practices (2 Point Checks)
SQL Server Wait Statistics Analysis and Strategy (5 Point Checks)
Error Log Review (3 Point Checks)
Miscellaneous Performance Checks (3 Point Checks)
Hi Steve,
Thanks for asking the question.
I usually cover them during my consultancy engagement.
I have blogged about those all these years on this blog. However, in the future, I will write more series about this topic.
Thanks,
Thanks for the post Pinal. I was surprised to see this subject and it immediate ly piqued my interest.
I also use indexes as a last resort and only it the gain is more than 50%.
I will definitely do some inspections on our servers, maybe I am Lucky and gain my company a few raster databases (And Warning my “Optimise Prime” nickname.
And I am getting myself that mug as a reward:-D