Voice of the DBA

Tag: T-SQL

Enabling an Index: #SQLNewBlogger
I don’t do a lot of work with disabled index, but I learned how to re-enable one today, which was a surprise to me. This short post covers how this works.

The Scenario

Imagine that you have an index on a table. In my case, I created this index:
```
CREATE INDEX LoggerNCI ON dbo.Logger (LogID)
```
I can then disable this index with the following code:
```
ALTER INDEX LoggerNCI ON dbo.Logger DISABLE
```
I had assumed that ENABLE would be the opposite, but SQL Prompt taught me this wasn’t an option. I checked the docs, and sure enough, it’s not ENABLE.

It’s resume. This code turns the index back on and updates it.
```
ALTER INDEX LoggerCI ON dbo.Logger REBUILD
```
I can also use either of these items:
```
CREATE INDEX LoggerNCI ON dbo.Logger (logid) WITH DROP_EXISTING

DBCC DBREINDEX(Logger, LoggerNCI)
```
Interesting short moment the other day as I realized there are a few options here.

SQL New Blogger

While playing with this, I realized that I didn’t know all the ways this worked, so I spent 10 minutes after I’d worked with the code to put this together.

A nice short way to showcase some learning.
October 2, 2024
Comparing an Old Running Total to Window Functions
Often I see running totals that are written in SQL using a variety of techniques. Many pieces of code were written in pre-2012 techniques, prior to window functions being introduced.

After SQL Server 2012, we had better ways to write a total. In this case, let’s see how much better. This is based on an article showing how you might convert code from the first query to the second. This is a performance analysis of the two techniques are different scales..

Pre SQL Server 2012

The old way:
```
SELECT Acc.ID,CONVERT(varchar(50),TransactionDate,101) AS TransactionDate
  , Balance, isnull(RunningTotal,'') AS RunningTotal 
 FROM Accounts Acc  
   LEFT OUTER JOIN (SELECT ID,sum(Balance) AS RunningTotal 
                    FROM (SELECT A.ID AS ID,B.ID AS BID, B.Balance 
                           FROM Accounts A 
                             cross JOIN Accounts B 
                           WHERE B.ID BETWEEN A.ID-4 
                           AND A.ID AND A.ID>4 
                          )T
                    GROUP BY ID ) Bal 
     ON Acc.ID=Bal.ID
```
What were the statistics on this? After running a few times, with STATISTICS IO ON, I get this:

Table ‘Accounts’. Scan count 37, logical reads 37, physical reads 0

Not bad. I’ve truncated out the other values as they were all 0.

Window Functions

Here is the same query written with a Window function.

SELECT
id
, TransactionDate
, Balance
, CASE WHEN LAG(TransactionDate, 4, null) OVER (ORDER BY TransactionDate) IS NOT NULL
THEN SUM (Balance) OVER (ORDER BY TransactionDate ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
ELSE 0
END AS runningotal
FROM dbo.accounts

The statistics?

Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0
Table ‘Accounts’. Scan count 1, logical reads 1, physical reads 0

The window function definitely does less work. A lot less. But how does this scale?

Performance Testing

There are numerous ways to create some test data for this. Since I have Redgate SQL Data Generator, I decided to use that. It’s simple and easy, and I added 100,000 rows first.

My results of the first query:

Lots of reads and scans. Let’s compare this to the window function.

Hmmm, both took essentially zero time less than a second. That might lead some developers to think either method is quick enough.

Let’s add 1mm more rows.

Now compare. The first takes about 15s with these results.

The window function? 3 sec, with these stats.

The comparison looks like this. First, let’s look at SSMS time

Old, Cross Join Window Function

20 rows 0 sec 0 sec

100,020 rows 0 sec 0 sec

1,100,020 rows 15 sec 3 sec

If we look at CPU time, then we see this:

Old, Cross Join Window Function

20 rows 0 ms 0 ms

100,020 rows 748 ms 250 ms

1,100,020 rows 5845 ms 2919 ms

If we look at the logical reads in total, we see this

Old, Cross Join Window Function

20 rows 37 1

100,020 rows 403,998 359

1,100,020 rows 6,450,895 3945

Clearly the window function is better and the better grows as the size of data grows.

Summary

This post looks at two queries and compares the performance across a few queries. These aren’t the only ones, and you might choose other types of queries, but these are both examples of how you might approach a problem using old tech and new tech.

The window function is not slightly more efficient, but extremely efficient compared to the older style method of using a cross join. As the data scales up, the difference is pronounced. While 1mm rows might not be a great test here, and you may prefer to test at 10mm or 100mm rows to get an idea of load, the fact is the Window function is much quicker and uses less resources.

If you are using older style code to perform T-SQL calculations, make some time to refactor that code (and test it) to use modern window functions.

Setup Code

Here’s the initial setup code:
```
CREATE TABLE Accounts
(
ID int IDENTITY(1,1),
TransactionDate datetime,
Balance float
)
go
insert into Accounts(TransactionDate,Balance) values ('1/1/2000',100)
insert into Accounts(TransactionDate,Balance) values ('1/2/2000',101)
insert into Accounts(TransactionDate,Balance) values ('1/3/2000',102)
insert into Accounts(TransactionDate,Balance) values ('1/4/2000',103)
insert into Accounts(TransactionDate,Balance) values ('1/5/2000',104)
insert into Accounts(TransactionDate,Balance) values ('1/6/2000',105)
insert into Accounts(TransactionDate,Balance) values ('1/7/2000',106)
insert into Accounts(TransactionDate,Balance) values ('1/8/2000',107)
insert into Accounts(TransactionDate,Balance) values ('1/9/2000',108)
insert into Accounts(TransactionDate,Balance) values ('1/10/2000',109)
insert into Accounts(TransactionDate,Balance) values ('1/11/2000',200)
insert into Accounts(TransactionDate,Balance) values ('1/12/2000',201)
insert into Accounts(TransactionDate,Balance) values ('1/13/2000',202)
insert into Accounts(TransactionDate,Balance) values ('1/14/2000',203)
insert into Accounts(TransactionDate,Balance) values ('1/15/2000',204)
insert into Accounts(TransactionDate,Balance) values ('1/16/2000',205)
insert into Accounts(TransactionDate,Balance) values ('1/17/2000',206)
insert into Accounts(TransactionDate,Balance) values ('1/18/2000',207)
insert into Accounts(TransactionDate,Balance) values ('1/19/2000',208)
insert into Accounts(TransactionDate,Balance) values ('1/20/2000',209)
go
```
September 30, 2024
Bad Stored Procedures

I don’t see a lot of SQL at The Daily WTF, but this one was great. It’s a stored procedure that was likely just converted from embedded code, as noted by the poster. It’s a strange set of code, that doesn’t quite make sense to me, and I can’t imagine why someone wrote it. Arguably, this is no better than having this code in a C# or ASP.NET application.

Or is it?

I think it is better. If I saw this code in a review or even in a production database, I could work on cleaning it up, adding protection against SQL Injection, and even tuning how it works to reduce the load on the database. I could likely wrap testing around this and get it deployed way quicker than if I were trying to update the source code for an app. More importantly, this is centralized code. If this is called from multiple places in the app code, I’ve fixed it once, not requiring an app developer, who has other work being piled on them, to spend time updating repeated sections of the code.

Even better, I could refactor some of the schema behind this stored procedure and easily find that my changes might affect this code. I could add a feature flag to this procedure and slowly migrate my schema in the background, without disturbing the user, again because the code is centralized. That’s a technique that most developers use in C#/Java/Python/etc., so why not in SQL?

I find it very interesting that a lot of developers refactor their classes and methods to better adhere to SOLID or some other practice, and they are happy to remove repeated code in their language. Yet, they don’t want to implement a stored procedure or function into their calls, essentially creating a database method for the things they need.

The more I work with legacy systems, the more value I see in using stored procedures. Every developer ought to know how to build them, and every developer ought to be able to create them in dev systems so they can easily deploy database and code changes together. More importantly, they can also share the load of tuning queries with operations staff, who may notice things in a production environment that are not apparent in development ones.

The big challenge in all of this is that database tooling is immature. Capturing your database code in source control is hard, and often it is a separate process from the one you follow for application code. I see some companies (including my employer) trying to make this easier, but there is a long way to go, and a lot of habits to change for developers.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

September 20, 2024
Finding Where xp_cmdshell is Used

I saw a post recently where someone was concerned about where xp_cmdshell was in use inside their system. They felt it was a security risk, and decided to get rid of it. I don’t agree with that, and I think xp_cmdshell can be safely used, by restricting who can run it.

That being said, I was happy to help. I saw someone say search in sys.modules, but that’s not enough. This post looks at what I thought was a better solution.

When you run a query like this one, you only search in the current database.

SELECT definition

FROM sys.system_sql_modules

WHERE definition LIKE ‘%xp_cmdshell%’;

This is fine if you’re concerned here. If I run this on a sample database, I find this:

However, that misses a few things. First, system_sql_modules isn’t everything. In this case, I have a proc that runs xp_cmdshell that doesn’t show up. I need all_sql_modules. This has user stuff. If I run that, I see this.

However, that’s one database. What is better?

All databases.

To do that, we’ll use the undocumented, but useful, sp_msforeachdb. In this, I can run code as a parameter. I can do this:

EXEC sp_msforeachdb ‘use ? SELECT definition FROM sys.all_sql_modules WHERE definition LIKE ”%xp_cmdshell%”;’
GO

The problem is I see this:

In the 4th result set, where are these things?

A better piece of code actually tells me which database is in use.

Here’s the code I ran. Note that I use the current database parameter, the question mark, in the SELET as well as the USE.

EXEC sp_msforeachdb ‘use ? SELECT ”?”, definition FROM sys.all_sql_modules WHERE definition LIKE ”%xp_cmdshell%”;’
GO

That gets me code inside databases, except for one place. What about jobs? I need this code:

USE msdb
GO
SELECT s2.job_id, s2.name, s.step_name FROM dbo.sysjobsteps AS s INNER JOIN dbo.sysjobs AS s2 ON s2.job_id = s.job_id
WHERE command LIKE ‘%xp_cmdshell%’

These two queries will get me the places I’ve used xp_cmdshell.

As long as I haven’t encrypted procs/functions. In that case, I need SQL Compare.

July 31, 2024