First of all, there is no stats3. SQL Server never stuffs in flight stats to stats blob for use during online index rebuild. Even you are under dirty read, you won’t get non-existing stats3.
You may want to find for example the date of the 4th Saturday in each month for a given year. This function came out of answering the question here: http://stackoverflow.com/questions/33694768/how-to-get-list-of-2nd-and-4th-saturday-dates-in-sql-server.
I’ve created it as a Table Valued Function so you can bind it into any query you wish.
Tony created a Table-Valued Function, which is handy but leads me to the classic User-Defined Function reminder: they tend to cause performance problems. One alternative is a dedicated date table with attributes like day of week and nth day of month.
Here’s one of my favorites, which searches for code within stored procedures, functions, and views:
SELECT OBJECT_SCHEMA_NAME(sm.object_id) AS SchemaName, OBJECT_NAME(sm.object_id) AS ObjectName, CONCAT(OBJECT_SCHEMA_NAME(sm.object_id), '.', OBJECT_NAME(sm.object_id)) AS FullName, CONCAT(OBJECT_SCHEMA_NAME(sm.object_id), '.', OBJECT_NAME(sm.object_id), ',') AS CommaSeparatedName, definition FROM sys.sql_modules sm WHERE sm.definition LIKE '%DEFINITION%' --AND OBJECT_SCHEMA_NAME(sm.object_id) = 'Something' --AND OBJECT_NAME(sm.object_id) = 'Something' ORDER BY SchemaName, ObjectName;
Shawn Melton shows us how to make SQLPS load a bit faster, and which comes with the obligatory warning:
WARNING: You are modifying the files at your own risk. You have been warned.
If you are not familiar with the files involved with a module, you can read more on that here. The file I found most interesting is the “SqlPsPostScript.PS1” file, located in the SQLPS module folder for the given version of SQL Server:
Check it out. Those two seconds you save add up over time.
This is effective, but I struggle a little with the SQL query. It’s good, but suffers from the structure of the jobs tables in MSDB. We have to account for that and it makes the SQL query a little convoluted. It would be helpful if we could reference a simple data set like the Job Activity Monitor in SSMS.
Of course, this is a leading question on my part. There is a way to do this and it is by leveraging the SQL Server Management Objects (SMO). This .Net library is the API interface for working with SQL Server and is what SSMS is built on. Because it is a .Net library, we can also access it through Powershell.
SMO’s a powerful thing.
Andy Galbraith has a tale of woe and a cautionary message:
Paul’s blog post “Issues around DBCC CHECKDB and the use of hidden database snapshots” discusses the need to have certain permissions to be able to create the snapshot CHECKDB uses. I checked the DATA directory and the SQL Server default path and found that the service account did have Full Control to those locations.
What happened next ultimately resolved my issue, and it reflects something I constantly tell people when they ask me how I research things relatively quickly (most of the time anyway :)) – whenever you read a blog post or article about a subject, MAKE SURE TO READ THE FOLLOW-UP COMMENTS! Sometimes they are nothing beyond “Great Article!” but quite often there are questions and answers between readers and the author that add important extra information to the topic, or just “Don’t Forget This!” style comments that add more detail.
If you have an Azure account (possibly through your MSDN subscription) here is the easiest way to get up and running with SQL Server 2016.
First go to the Azure Portal – http://portal.azure.com
Search and find the SQL Server 2016 CTP3 in the Data and Analytics Marketplace in Azure.
My preference is to grab the ISO and build a local VM, or install it on a server in my environment. But if your server infrastructure lives on Azure or you’ve got those MSDN credits to burn, this is a good alternative.
The dilemma we all run into is what level of SAMPLED statistics is appropriate? The answer is you have to test but that is not always feasible and in the case of Microsoft CSS we generally don’t have histogram, historical states to revisit.
Microsoft CSS is engaged to help track down the source of a poorly performing query. It is common step to locate possible cardinality mismatches and study them closer. Studying the statistics dates, row modification counter(s), atypical parameters usage and the like are among the fundamental troubleshooting steps.
Good post and great scripts, even if he Microsoftly nouns the verb “ask.”
Every time I install a new version of SSMS I make a handful of changes to the default setup. For my own notes, and in case anyone is interested in some of the things you can do with SSMS I thought I’d post a list of those changes.
I also use a darker theme, very similar to Fisher’s; mine is designed to look like vim blue. Of course, personal SSMS settings are personal.
Temporal tables allow us to retrieve the state of a table, at a specific point in time, using a method called effective dating. Not only useful for auditing and forensics, temporal tables can help if data is accidentally deleted, or perform trend analysis in a simpler way.
I’m hoping there’s an entry on the potential performance impacts.