Tag: SELECT

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,

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

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

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

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

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

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

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

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

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

Tagged with: , , , , , , ,
Top