Removing a filegroup that contains data.
Occasionally, you may find you have an extra filegroup that is no longer required for whatever reason. In order to remove that filegroup, you must move all data from that filegroup to a new filegroup….
How can I automate statistics management?
Occasionally, it can be beneficial to turn off AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS, and manage these items yourself. I’ve created the following stored procedure, which lives in master to allow it to be called from the context…
Missing Index DMVs have a limited capacity. How can I work around that?
The system dynamic-management-views for tracking index suggestions are limited to displaying 500 indexes at most. In a busy system with either a lot of databases or a lot of tables, this can be substantially limiting….
Wait Stats over Time
Looking at Wait Stats over time while a specific piece of work is taking place can be a very helpful method for troubleshooting performance issues. SQL Server exposes wait stats so you can see what…
Identify S-1-9-3 logins in sys.dm_exec_sessions
When looking at sys.dm_exec_sessions to see who is logged into a SQL Server instance, you may notice the login_name column contains a SID such as S-1-9-3-1474169822-1205489898-2971455952-561433448. This can happen as a result of using EXECUTE…
Tool for scripting SQL Server Agent Jobs
I recently created an open-source tool that will create a T-SQL script file containing code to recreate whatever jobs you presently have in SQL Server Agent for any given SQL Instance, up to SQL Server…