At work we lost our super star DBA some months back, and though he is sorely missed, this has created opportunities for me to grow in my SQL Server knowledge. One of the key components of our Business Intelligence environment (my world!) is our replication between PROD and the Data Warehouse. Because this replication is … Continue reading Setup a Simple Transactional Replication lab
Category: DBA
Can Table Partitioning Improve Query Performance?
At work we have tables containing billions of rows, and are hundreds of GB in size. The business intelligence queries we run against them in the reporting database are often focused around dates and not accountids / productids as they are in PROD. For these large tables, it is sometimes useful to split the records … Continue reading Can Table Partitioning Improve Query Performance?
How much space will table compression save me?
At work we have a reasonably large table with a NVARCHAR(MAX) column. This column stores HTML and other user generated content. This table is on a SQL Server 2014 instance, so cannot take advantage of new compression technologies in SS 2016. Looking at the estimated benefits via query: Well … bummer! Looks like no savings … Continue reading How much space will table compression save me?
Create an index on a table in Transactional Replication
Not long ago I took over a database that uses Transactional Replication. Not long after I needed to add an index. So here is the syntax and misc I found for doing this: The important point is that you use ONLINE=ON, and since our TEMPDB is on SSD, go ahead and use SORT_IN_TEMPDB.
Get rid of blank lines in SSMS
I often copy paste the output of "sp_helptext" for some of our stored procedures. For whatever reason, they often paste with numerous blank lines between each meaningful section of code. Here is how I get rid of those lines:
