Tag: create index in sql server

Updates To My SQL Server Performance Troubleshooting Scripts

Posted on by Erik Darling

Snappy Name

It’s been a busy few months working on scripts in my GitHub repo, including adding two new members to the family.

  • sp_IndexCleanup: Easily deduplicate indexes — scripts all the changes out for you!
  • sp_PerfCheck: A high-level review of performance-related settings and configurations!

Here are the main changes from lovely contributors:

  • sp_QuickieStore: Appended _ms where it was missing for avg_cpu_time by @ReeceGoding in https://github.com/erikdarlingdata/DarlingData/pull/600
  • sp_QuickieStore: Moved validation of @sort_order to be much earlier on. by @ReeceGoding in https://github.com/erikdarlingdata/DarlingData/pull/602
  • sp_QuickieStore: Moved special sorting columns for special sort order values to be jus… by @ReeceGoding in https://github.com/erikdarlingdata/DarlingData/pull/604
  • sp_QuickieStore: Made plan hash, query hash, or sql handle show when the parameter for filtering them out is passed in by @ReeceGoding in https://github.com/erikdarlingdata/DarlingData/pull/608
  • sp_QuickieStore: Documents that @format_output = 1 removes most decimals. by @ReeceGoding in https://github.com/erikdarlingdata/DarlingData/pull/606
  • Adding support for indexed views in addition to indexes on tables. by @FirstCall42 in https://github.com/erikdarlingdata/DarlingData/pull/610
  • sp_QuickieStore: Move regression validation to just after sort order by @ReeceGoding in https://github.com/erikdarlingdata/DarlingData/pull/612
  • Made sp_HumanEventsBlockViewer not error out when system_health is used by @ReeceGoding in https://github.com/erikdarlingdata/DarlingData/pull/618

You can download all the scripts here.

Happy tuning!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

My Upcoming Speaking Schedule

Posted on by Erik Darling

Busy Summer

The nice folks at Red Gate have decided to put me to work.

That means I’m going on tour, and maybe getting some socks and a Hawaiian shirt.

No word on a “Lego Erik” yet.

PASS On Tour Events:

PASS Data Community Summit:

Of course, Kendra Little and I are back in action to teach back-t0-back T-SQL precons.

 

See you out there!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Learn T-SQL With Erik: TOP and OFFSET/FETCH

Posted on by Erik Darling

Learn T-SQL With Erik: TOP and OFFSET/FETCH

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Learn T-SQL With Erik: ORDER BY

Posted on by Erik Darling

Learn T-SQL With Erik: ORDER BY

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

SQL Server Performance Office Hours Episode 16

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 16


To ask your questions, head over here.

I’ve heard you talk about your career path a few times, and it’s pretty weird. Do you have any regrets? Are you still happy with what you do?
Do you have differing approaches for performance tuning an OLAP system vs an OLTP system?
Do you know of any disadvantages of using a filtered index to filter NULL values? We have a very heavy transactional table, like 10k trans/sec, with a clustered index and one non-clustered index. We don’t have any queries that select rows with NULL values ​​from this table. The DBA team said we should avoid using a filtered index without any proof. What do you think?
In all your demos you compress (page) your indexes. Do you default to that with your all your client workloads? Do you see more benefit than a negative impact in your experience? Thanks!
I’ve seen you suggest columnstore for paging and dynamic searches. How do you make your non-clustered columnstore indexes perform acceptably on tables where all of the data is hot?

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Learn T-SQL With Erik: A Neat GROUP BY Trick

Posted on by Erik Darling

Learn T-SQL With Erik: A Neat GROUP BY Trick

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Learn T-SQL With Erik: Stupid OUTPUT Stuff

Posted on by Erik Darling

Learn T-SQL With Erik: Stupid OUTPUT Stuff

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Learn T-SQL With Erik: DISTINCT Isn’t Always Evil

Posted on by Erik Darling

Learn T-SQL With Erik: DISTINCT Isn’t Always Evil

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Learn T-SQL With Erik: Solving Problems With APPLY

Posted on by Erik Darling

Learn T-SQL With Erik: Solving Problems With APPLY

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

SQL Server Performance Office Hours Episode 13

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 13

What is the effect of having a few queries using Read Committed isolation level where the database is set to RCSI? Will those dumb Read Committed queries block any others?
Do you have any suggestions for optimizing data retrieval using Full Text Search (FullTextMatch TVF)? We’ve tried cutting down the query to reduce the dataset using other parameters before the FTS bit and also breaking out FTS across multiple columns to do one at a time, but anything else? Also, the memory used by FullTextMatch is hard to define. Should we reduce the size of the BufferPool to give more to FTS or the other way around? Thank you for coming to my Ted Talk on FTS.
If I were to want to kill all the table partitions by fire, what brand of flamethrower do you recommend?
Do you know of any documentation that makes it very obvious what Max Server Memory controls? It’s changed across versions. I think it used to just be the buffer pool?
I am writing C# classes and building CLRs for string comparisons, then creating a TVF for execution. The TVF is creating row estimates of exactly 1000x my row actuals (2 rows creates a “2 of 2000” for example). No query hints seem to resolve this, how would you work to control these estimates from my compiled C#?

To ask your questions, head over here.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.