I have been writing about how some of the interesting enhancements made with SQL Server 2016 SP1 have caught my attention. This one is a killer feature that can be of great value when you are trying to do testing. Having said that, I also want to call out that this feature has been added with SQL Server 2014 SP2 too. When you get into performance problems related to the query optimizer, then this capability will be of great advantage. In this blog post we will learn about Generate Statistics and Schema Only Copy of the Database.
SQL SERVER – Fix: Error: Msg 1904 The statistics on table has 65 columns in the key list
With SQL Server 2016, I have come to know some of the restrictions which were applicable earlier are no longer the limits to look for. In one such experimentation is what I stumbled upon the blog post: SQL SERVER – Fix: Error: Msg 1904, Level 16 The statistics on table has 33 column names in statistics key list. The maximum limit for index or statistics key column list is 32.
I thought having 32 columns itself was something far too many but to my surprise when I used the same script from the blog to just realize how this error was not popping up.
How to Find Outdated Statistics? – Interview Question of the Week #081
Recently in one of the webcast, while I was presenting about statistics and performance tuning, I was asked by one of the attendees that if there is anyway to find out all the outdated statistics based on the dates from the server. This is indeed a very interesting question and I will be happy to explain you here.
SQL SERVER – The NOLOCK Question – Notes from the Field #117
[Note from Pinal]: This is a 117th episode of Notes from the Field series. Everytime I go to do performance tuning consultancy, one thing annoys me and makes me angry. The usage of NOLOCK hint. I think without any argument, this is one of the most abused query hint in the SQL Server universe. Lots of people treat NOLOCK as a silver bullet of performance tuning and that is unfortunate. There is a special purpose of how NOLOCK should be used and what is the end goal of it.
In this episode of the Notes from the Field series database expert Stuart Ainsworth explains about NOLOCK and its best practices. Trust me, you do not want to overuse NOLOCK hint as it can just impact your database integrity negatively.
SQL SERVER – Three Simple Guidelines for System Maintenance – Notes from the Field #098
[Note from Pinal]: This is a 98th episode of Notes from the Field series. Maintenance of the database is a very critical activity and I have always seen DBA taking it very seriously. There is a only one problem – there is no single solution or guidance for how to maintain the database. Everybody has their own opinion and way to do different tasks. System maintenance is very straight forward task but I have seen quite often experts even getting confused with the same. Many only focus on index maintenance, statistics maintenance and a few other tasks are common, but understanding the real philosophy of this task is something beyond indexes. When I asked my good friend Stuart about system maintenance, he came up with very interesting guidelines for system maintenance.
SQL SERVER – Reading Statistics IO data with SQL Server
I am a supporter of always going to the basics when it comes to performance troubleshooting. Whenever I have done any perf tuning exercise, I start by enabling the Statistics IO as part of my debugging. When STATISTICS I/O is enabled, SQL Server maintains I/O statistics for the queries on…
Read MoreSQL SERVER – Basic Statistics Maintenance – Notes from the Field #083
[Note from Pinal]: This is a 83rd episode of Notes from the Field series. Maintenance of the database is very critical activity and I have always seen DBA taking it very seriously. There is a only one problem – there is no single solution or guidance for how to maintain the database.…
Read More