While trying to debug performance for an application the other day, I noticed that the expected behavior for the application was to use Read Committed Snapshot Isolation level, however when a query was run it seems that it is running in isolation level Read Committed. Why the change? Testing the problem I’m using the Stack Overflow database […]
Kendra Little over at SQL Workbooks recently answered the question “When should I use explicit transactions for single statements?” I’m not going to re-hash what Kendra wrote, go and read it on her site. What I want to do is add another case for when I use explicit transactions for a single statement. Firstly, what is an […]
A few years ago I needed to join a table to the result of a table-value function and came across Cross Apply. Cross Apply and Outer Apply were introduced back in SQL 2005.
I always think it’s a great idea to ask for help. When I was a kid, my parents taught me that it’s always a good idea to ask for help – the worst that will happen is they’ll say no. In IT there is too much to learn so I always think it’s a great idea […]
I had a question yesterday about whether or not you can use a case statement as part of a join condition. I was pretty sure the answer was yes, but we can always just run a simple test to prove it. It’s a pretty simple test to setup, I’m going to create 2 tables, populate them […]
Like a lot of companies that use Windows, my PC is connected to a domain and this is great when I’m connecting to the companies SQL Servers using Windows Credentials in SSMS because it “just works”™. But sometimes I need to VPN to another domain and connect to SQL Servers on the other domain using Windows Credentials. […]
While looking at long running queries today I noticed that one of our worst performing queries was a monitoring tool that checked when the last backup ran. SELECT database_name = DB_NAME(DB_ID([database_name])) , type = [type] , backup_start_date = MAX([backup_start_date]) , backup_finish_date = MAX([backup_finish_date]) FROM msdb..backupset WHERE DB_ID([database_name]) IS NOT NULL GROUP BY DB_NAME(DB_ID([database_name])) ,[type] I had […]
We use Change Data Capture (cdc) in our production environment on a few tables so we can do some asynchronous processing of data. Our developers have a pre-deployment environment which is a nightly restore of our nightly backups (this has an added bonus of testing our nightly backups). Usually the testers don’t care about testing the CDC […]
We run Analysis Services on the same server as an asynchronous copy of SQL (mainly for licensing reasons). We have been having problems when processing the cube that it drops the TCP connection to SQL. It was suggested that we use Named Pipes instead of TCP connections. We don’t want to turn off TCP completely, so […]
