concept script in category sql

appears as: script, scripts, The script, scripts, script, The scripts, A script, The script
SQL Server MVP Deep Dives Vol. 2

This is an excerpt from Manning's book SQL Server MVP Deep Dives Vol. 2.

When the time comes for the DBA to execute the script, everything seems fine. But on Monday morning the users are inundating the help desk with calls that the application isn’t returning the data expected and the DBA team is now involved. As it turns out, the script not only added objects, but also updated the biggest and most used table’s data with an unqualified WHERE clause that modified every row. Because you’re the DBA, you’re automatically in the hot seat: it’s up to you to get the data back. Although you have backups, the database is 1 TB and it’ll take some time to get the file from the centralized backup solution and restore it.

Figure 5. Custom folder and script added to Template Explorer.

Let’s look at a concrete example. To set it up, run the script in listing 1 to populate a test table with one million rows.

Listing 1. Creating a test table and populating it with a million rows
CREATE TABLE test (i INT, j INT, filler CHAR(2000)) go WITH tmp(i, j, filler) AS ( SELECT CAST(1 AS INT), CAST(1 AS INT), 'abc' UNION ALL SELECT i + 1, j+1, filler FROM tmp WHERE i < 1000000 ) INSERT test SELECT * FROM tmp OPTION (maxrecursion 0) go CREATE CLUSTERED INDEX cix_test ON test(i) CREATE INDEX ix_test ON test(j) go

Then, run the script in listing 2 to get the average length of the filler column. This is to force a table scan (i.e., clustered index scan) through the test table because the filler column isn’t indexed.

Listing 2. Scanning the test table with the clustered index
DBCC DROPCLEANBUFFERS go SET STATISTICS PROFILE ON SET STATISTICS IO ON go SELECT AVG(LEN(filler)) FROM test OPTION(MAXDOP 1) go SET STATISTICS PROFILE OFF SET STATISTICS IO OFF go

For comparison, run the script in listing 3. This time, use an index hint to force SQL Server to use the nonclustered index.

SQL Server DMVs in Action: Better Queries with Dynamic Management Views

This is an excerpt from Manning's book SQL Server DMVs in Action: Better Queries with Dynamic Management Views.

It’s often the case that you have several different databases running on the same server. A consequence of this is that no matter how optimal your individual database may be, another database on the server, running suboptimally, may affect the server’s resources, and this may impact the performance of your database. Because of this, we offer scripts that inspect the DMVs across all the databases on the server. It’s possible to target the queries to a specific database on the server instance by supplying a relevant WHERE clause (many other filters can be applied).

The scripts contained in this chapter will improve the performance of your slow-running SQL queries. They accomplish this by recompiling routines that have become slow, improving the retrieval of data from your indexes, intelligently updating index statistics upon which your optimal query plans will be based, and creating or removing indexes as necessary.

Updating an index’s statistics takes time. Having provided an algorithm to obtain a sampling of rows, it makes sense to provide a script that adjusts the sampling to fit a given time frame; for example, update as many statistics as possible in 30 minutes. The script we use to automatically update the statistics intelligently, using a time-based approach, is shown next.

SQL Server MVP Deep Dives

This is an excerpt from Manning's book SQL Server MVP Deep Dives.

Start by executing the scripts in listing 1, which will create our six databases.

Listing 1. Script to create a login with identical SID on the mirror instance
-- Get the SID for an existing login SELECT [name], sid FROM sys.server_principals WHERE type_desc = 'SQL_LOGIN' -- Create SQL Server login with a specific SID --(to avoid orphaned user problem) CREATE LOGIN yourloginname WITH PASSWORD = 'yourpassword', sid = 0xA306A5DFBF321A4D98D71520DAE1C1D3, CHECK_POLICY = OFF