Optimize for Ad Hoc Workloads

The Optimize for Ad Hoc Workloads server configuration can improve performance, and is extremely unlikely to negatively impact performance. This was a new feature that was introduced in SQL Server 2008, and as with many new features in SQL Server,

Posted in SQL Server Tagged with: , , , , ,

mssqlsystemresource Database

I was looking through my SQL Server error logs to confirm that CheckDB was being run as I had scheduled based on my previous post to run DBCC CheckDB on all databases. I wanted to confirm that there was no

Posted in SQL Internals Tagged with: , , , ,

Status of DBCC CheckDB

So you are checking your database with DBCC CheckDB and of course if you are like me you use the WITH NO_INFOMSGS parameter. But it turns out that CheckDB is taking longer to run that you expected, and you want

Posted in Corruption Tagged with: , , ,

SQL Server Script to Display Job History

Its not always quick and easy in SQL Server to get a full list of the jobs that have been run, when they were run and how long they took. I created the following script to quickly check on the

Posted in SQL Server, TSQL Tagged with: , , , ,

What is a Page Split

Tables, and indexes are organized in SQL Server into 8K chunks called pages. If you have rows that are 100 bytes each, you can fit about 80 of those rows into a given page. If you update one of those

Posted in Performance, Performance Tuning Tagged with: , , , , , , ,

Deleting from a CTE with an EXISTS statement

During my 24 Hours of Pass presentation on Advanced CTE’s today I was asked the question about deleting from a CTE when it uses an EXISTS statement that queries another table. I figured I would create quick blog post to show

Posted in CTE Tagged with: , , ,

Database Corruption Challenge Week 7 – Alternate Solution

The alternate solution to the Database Corruption Challenge this week was created by Patrick Flynn. This solution is the only solution to successfully recover all the data without using any of the backups. If the challenge had been structured differently

Posted in Corruption Tagged with: , , , , , , ,

Week 5 – Alternate Solution

Here is how I solved Week 5 of the Database Corruption Challenge. The following steps were tested and confirmed working on SQL Server 2008R2, SQL Server 2012, and SQL Server 2014.   To oversimplify, here are the steps: Restore the

Posted in Uncategorized Tagged with: , , , , , ,

Corruption Challenge Week 4 – The Winning Solution

Congratulations to Randolph West who won the corruption challenge this week with the following solution which restored all of the data. First he restored the database to get started. Note some of his code and comments have been reformatted to

Posted in Corruption Tagged with: , , , , , , ,

Using the TSQL CHOOSE Function

Here is a short video tutorial that shows how to use the CHOOSE function in T-SQL on SQL Server 2012, SQL Server 2014 or Newer. This was originally part of my free SQL query training for the 70-461 certification exam. Here

Posted in 70-461 Training Tagged with: , , , , , , ,