Press "Enter" to skip to content

Curated SQL Posts

Important Stored Procedures For Transactional Replication

Drew Furgiuele (who always starts off with the red ring) explains three important replication stored procedures:

We all have that one scenario where replication caused us some major headaches. It’s through these headaches, though, that the search for better ways to handle problems comes up. Through my time with replication, I’ve researched and used a lot of system tables, views, and stored procedures to diagnose or fix replication problems. There’s actually a lot of things you can get into if you’re so inclined. If you put me on the spot though, and asked me “Drew, what do you think are the most helpful replication internal objects to know?” I’d tell you I have three, and they’re all stored procedures.

Here then, in no particular order, are three system stored procedures that I feel any DBA that has to deal with transactional replication should at least be aware of. They each have a unique purpose, and each can be used to quickly monitor, fix, or even destroy replication forcibly, respectively.

Read the whole thing.

Comments closed

The Trivial Plan Problem With Columnstore

Niko Neugebauer shows that trivial columnstore plans can lead to poor performance, but SQL Server 2017 has a fix:

Do you remember one of the major problems in SQL Server 2014 using Columnstore Indexes ? It was the lack of the support for the Batch Execution Mode with just a single core. We would get wonderful, fast execution plans with MAXDOP >= 2, which will go terribly slow if there would not be enough memory to run the query with 2 or more cores, or if the internal query cost would be below the parallel execution threshold (cost threshold for parallelism)
OR if the execution plan would be dimmed as TRIVIAL by the Query Optimiser, thus getting a single core execution and running really slow.
Once we upgraded to SQL Server 2016, the problem of inability of the single core Batch Mode execution would fade away, but still, sometimes some queries would run terribly slow for some reason …
One of the reasons behind this are the trivial execution plans, which are running Columnstore Index Scan in the Row Execution Mode – also known as a VerySlowExecutionMode for the big amounts of data.

Read on to see the change in 2017, as well as a workaround for 2016.

Comments closed

Time Slicing T-SQL

Bill Fellows has some fancy window function footwork to split data into time slices:

That looks like a lot, but it really isn’t. Starting from the first inner most query, we select the top 24 rows from sys.all_objects and use the ROW_NUMBER function to generate us a monotonically increasing set of values, thus 1…24. However, since the allowable range of hours is 0 to 23, I deduct one from this value (A). I repeat this pattern to generate minutes (B) except we get the top 60. Since I want 15 second intervals, for the seconds query, I only get the top 4 values. I deduct one so we have {0,1,2,3} and then multiply by 15 to get my increments (C). If you want different time slices, that’s how I would modify this pattern.

Only works on 2012 or later, but it’s a fancy way of slicing into 15-second (or however you define it) chunks.

Comments closed

The Great Custom Visual Move

Ginger Grant runs into a problem with some of the custom visuals (not) in the Office Store:

This week I decided to do a demo using the Aquarium custom visual.  As readers of my blog know, I have used the custom visual before, but it has been a while and I have changed PCs since then.  No worries I can always go download the visual from the store, right? Wrong. The aquarium visual is not available on the new store. Neither is Image Viewer, if one is looking to add that into your latest Power BI report it is not available. What happened?

Read on to learn why the aquarium is missing.  RIP aquarium (hopefully only temporarily).

Comments closed

Querying MSDB For Backup Information

Lori Brown shows how to query the msdb database and get information on backups, including whether the backups are compressed:

We recently started using a third party software to do our in-house SQL backups so that the backup files are stored in a redundant and safe place. To confirm that the software was indeed compressing backups as it stated it would, we wanted to see what each backup size actually was in SQL so that we could compare that to what the software was telling us.

SQL stores lots of handy backup information in msdb in the backupset and backupmediafamily tables.

There’s some useful information in these tables, though make sure you clean them up regularly or else msdb can become enormous.

Comments closed

R6 Classes In R

David Smith explains what R6 classes are in R:

The big advantage of R6 is that it makes it much easier to implement some common data structures in a user-friendly manner. For example, to implement a stack “pop” operation in S3 or S4 you have to do something like this:

x <- topval(mystack)
mystack <- remove_top(mystack)

In R6, the implementation is much simpler to use:

x <- mystack$pop()

David links to some good resources on the topic, so check those out as well.

Comments closed

Finding Running Agent Jobs

Adrian Buckman has a script to find all running SQL Agent jobs:

For our Procedure we wanted to show currently running jobs regardless of run time or run time vs historical run time, we also wanted to be able to see if the job was started by the Agent itself or a User, and to see which step the job is currently running on including that steps Elapsed time and last but not least the Total job elapsed time.

Now I will be the first to admit , this is not the prettiest code I have ever produced but getting some of this information out is quite tricky 🙂

This Procedure is a great alternative to the SQL Agent Activity Monitor, it doesn’t have all the information that the Monitor has but it probably has everything that you need to see at a glance – and whats more being that it is a Stored Procedure you could run this across multiple servers via registered servers for example and get results within seconds.

Click through for the script.

Comments closed

Powershell To Copy Reporting Services Subscriptions

Claudio Silva has a new contribution to the Reporting Services Powershell module:

If we take a look to the “New Subscription” form, we will discover about a dozen of fields that need to be configured. Doing this by hand can make you want to pull your hairs, also the probability of error is huge, even with copy & paste.
Who wants to do copy & paste of dozens of fields between reports? I know who doesn’t – me 🙂

Click through to learn more about Claudio’s cmdlets for getting, setting, and removing Reporting Services subscriptions.

Comments closed

Moving TempDB In Linux

David Klee shows how to migrate the tempdb database when running SQL Server on Linux:

We previously created a folder at /var/opt/mssql/data/tempdb01 for these files. Moving them is straightforward, once you know the file system structure. The following commands will move them to the new location, and I also add additional files to equal the four vCPUs I have on this SQL Server VM. The file growth is my model database’s default of 64MB for this instance. Do as you would normally do with SQL Server on Windows with tempdb file counts and separation of duties for your workload.

Read on for the process.  As a general spoiler, the “how to do this in Linux” answer is usually pretty close to the same as the “how to do this in Windows” answer, at least once you get into Management Studio.

Comments closed