My first MS SQL Server Bug Report

This morning filed my first SQL Bug report: https://connect.microsoft.com/SQLServer/feedbackdetail/view/973290/temporary-stored-procedures-have-execution-context-of-these-databases-where-they-were-created

Here is the problem:  
Temporary Stored Procedures have execution context of these databases where they were created.


What actually happens:
Temporary Stored Procedures, when created, remember the database, which was current at the time of their creation and use that database as current during the execution of select statements from system tables.

In other words: if you want to get list of database objects within temporary Stored Procedure you can do it only from specified database or from the database where that procedure was created.

Not sure if I explained it clearly, but here is a simple example:

USE msdb; GO CREATE TABLE tbl_VeryTestTable_123456789ABCDE(ID INT IDENTITY(1,1), A VARCHAR(10)); GO CREATE PROCEDURE #SP_VeryTemporaryProcedure_123456789ABCDE AS SELECT DB_NAME() as 'Current Database', 'Temp SP' as 'Output Source'; SELECT 'Temp SP' as 'Output Source', name FROM sys.objects WHERE name = 'tbl_VeryTestTable_123456789ABCDE'; RETURN; GO USE Master; GO EXEC #SP_VeryTemporaryProcedure_123456789ABCDE; GO USE msdb; GO DROP TABLE tbl_VeryTestTable_123456789ABCDE; GO DROP PROCEDURE #SP_VeryTemporaryProcedure_123456789ABCDE; GO

What that script does:
1. Makes "msdb" database as "current".
2. Create a table in "current" "msdb" database.
3. Create temporary Stored Procedure which returns name of the "current" database and searches for just created table (in step #2) within the current database context.
4. Change current database context to "Master" database.
5. Run the new temporary Stored Procedure.
    As a result you get two output data sets:
    A. Current database: "Master"
    B. The table will be found in "current database context"!!!!! 
6. 7. 8. Return to msdb DB and cleanup after yourself.

How did I find this:
I've tried to create a temporary Stored Procedure which would return list of objects from "current" database it worked perfectly until I changed the current database. Being in different database confused me, I couldn't see objects I expected to see there and couldn't find objects which were listed by the
Stored Procedure.

I assume that bug is not very annoying (who use temp SPs at all???)  and I do not expect MS to fix it any time soon. At least we have to live with it in SQL 2012 and 2014.

So, the simple workaround would be to use dynamic SQL. 
Here is an example how to do this:

CREATE PROCEDURE #SP_VeryTemporaryProcedure_123456789ABCDE AS DECLARE @SQL VARCHAR(1000); SELECT DB_NAME() as 'Current Database', 'Temp SP' as 'Output Source'; SELECT 'Temp SP' as 'Output Source', name FROM sys.objects WHERE name = 'tbl_VeryTestTable_123456789ABCDE'; SELECT @SQL = ' SELECT ''Temp SP against "'     + DB_NAME() + '" database.''  as ''Output Source'', name FROM ['     + DB_NAME() + '].sys.objects WHERE name = ''tbl_VeryTestTable_123456789ABCDE'';'; EXEC (@SQL); RETURN; GO

This Stored Procedure will query really Current database.

If you have any other Ideas please let me know.

Experimenting with "Bulk Insert" Fast load

Tried to use scientific approach in measuring data load speed using "Bulk Insert" command.

Experiment Goal:
Load text data file into SQL Server table as fast as possibly using "Bulk Insert" command with different parameters.
Experiment does not involve using indexes or any ETL activities - just simple load of one file to one table.

Experiment Environment:
SQL Server 2012 Enterprise

Experiment Preparations:
Generated 1.3 Gb file with 10 million records from sys.messages table.

Experiment Scenarios:
Variations of SQL Database transactional mode: Full, Bulk_Logged, Simple.
Variations of "BULK INSERT" command parameters: TABLOCK, ROWS_PER_BATCH, BATCHSIZE;
Using Pre-Growth for Data and Log files.

Used Script:

Experiment Metrics/Results:

Metrics explanation:
1. ## - Experiment number. Some experiments were omitted (blank lines).
2. Mode - SQL Database transactional mode (Full, Bulk_Logged, Simple).
3. "BULK INSERT" command parameters (TABLOCK, ROWS_PER_BATCH, BATCHSIZE). For "BATCHSIZE" were used following batch sizes: 1 million records, 0.1 million and 5 million.
4. Database Pre-Growth - Data and log files were pre-grown to following values, depending on expected results: 2500Mb for Data file and 6200MB, 2600MB and 100MB for Log file.
5. Experiment Timing in seconds - measured Full time of the experiment, including DB creation, Back Up file Pre-Growth, Data Load and drop of the database. Also measured time for Data Loading process itself.
6. File size growth - The final Data and Log files sizes. In some cases Log file growth indicated amount of space needed to perform the load.

Interpreting Results:
For all three transactional modes the best performance was achieved with use of "TABLOCK" option and pre-growing Data and Log files (see yellow lines).


Expected conclusions:
1. "TABLOCK" option behaved as expected and dramatically improved loading performance.
2. Pre-growing of Data and Log Files also significantly improved performance. That is another point against "Autoshrink" database option.

Unexpected conclusions:
1. "BULK INSERT" in FULL transactional mode can be as fast as in BULK_LOGGED and SIMPLE modes: 24 sec vs 22 sec. (See lines #6 and #16 & #26). That might be explained only by the fact that Data and Log files were created on different physical drives and writes into both files were dome in parralel.
2. Even though use of "ROWS_PER_BATCH" and "BATCHSIZE" parameters showed little performance improvement it was not significant comparing to "TABLOCK" option. Most probably that server just had enough memory to load whole file without necessity divide it in chunks.


Please ask any questions if you think experiment was not held properly or result interpretation is not clear.
Thanks.

Everything must "GO"

Use "GO" command daily uncounted number of times, but rarely with counter.

It is very useful feature when you want to run certain part of your code more than once.
Usually I fill out a table by some random values like this:

INSERT INTO tbl_Test_Load(ID, A)
SELECT NEWID(), REPLICATE('A',10);
GO 10

That code is supposed to fill out the table by 10 rows, because the statement before "GO" will be executed 10 times. Exactly as specified.

If I try to truncate table before its loading it will screw up everything:

TRUNCATE TABLE tbl_Test_Load;
INSERT INTO tbl_Test_Load(ID, A)
SELECT NEWID(), REPLICATE('A',10);
GO 10

That statement willbe also executed 10 times, but as a result we will have only one row int the test table because truncate statement will be interpreted as the part of batch and also will be executed 10 times.
In order to avoid that confusion we have to add extra "GO" between truncate and insert statements.

When I tried to insert more values than just 10-1000 records  I hit a performance issue.
Here is the test script:

USE tempdb

GO

SET NOCOUNT ON

GO

CREATE TABLE tbl_Test_Load(

       ID UNIQUEIDENTIFIER,

       A CHAR(10)

);

GO

PRINT '"GO" loop begins: ' + RIGHT(CONVERT(VARCHAR(30), GETDATE(), 121),12);

GO

INSERT INTO tbl_Test_Load(ID, A)

SELECT NEWID(), REPLICATE('A',10);

GO 100000

PRINT '"GO" loop Ends: ' + RIGHT(CONVERT(VARCHAR(30), GETDATE(), 121),12);

TRUNCATE TABLE tbl_Test_Load;

DECLARE @i INT = 100000;

PRINT '"While" loop begins: ' + RIGHT(CONVERT(VARCHAR(30), GETDATE(), 121),12);

WHILE @i > 0

BEGIN

  INSERT INTO tbl_Test_Load(ID, A)

  SELECT NEWID(), REPLICATE('A',10); 

  SET @i -= 1;

END

PRINT '"While" loop ends: ' + RIGHT(CONVERT(VARCHAR(30), GETDATE(), 121),12);

GO

DROP TABLE tbl_Test_Load;

GO

And here is the result of that script:
"GO" loop begins: 10:44:55.717
Beginning execution loop
Batch execution completed 100000 times.
"GO" loop Ends: 10:46:25.887
"While" loop begins: 10:46:25.887
"While" loop ends: 10:46:27.733

One and a half minute for "GO" loop and only two seconds for "WHILE" loop.
I was little bit surprised by that result.

That required an explanation. I looked in MSDN:

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO. 

GO is a utility command that requires no permissions. It can be executed by any user.

That explanation makes it clear. While "WHILE" loop is sent to a server and totally executed by the SQL Server engine "GO" loop is executed by a utility, in my case SSMS. Instead of one complex loop batch SSMS sent 10000 simple batches to SQL Server which is obviously slower, including handling the loop by SSMS.

Nice to know.

Building Future SQL application without "Deprecated Features"

That post is for SQL Developers, Development DBAs and DBAs who are responsible for application development or see themselves to be responsible for their systems in the future.

The value of this post might be measured in about 10-15 years, when applications, which currently in development stage, will be migrated to newer versions of SQL Servers.

Does anybody still have SQL Server 2000 still running in their environment just because application's code can't be run on newer SQL Server versions? Do you fill that pain?

If so, you have an opportunity to change the future of your application.

When one SQL developer or group of them write a code they might use some SQL Server "Deprecated Features", which will perfectly work in current environment and might work for another 10 years until somebody refabrish the code during migration to SQL Server 20 or 25.

That is tolerable pain, but just imagine if these "Deprecated Features" were used in dynamic queries by front-end apps. Java and C# developers have no idea about SQL features which won't be available in future versions and in 2020 your organization would have to spend another fortune to re-developing whole application or will live with ancient SQL Server 2014.


There are many ways to avoid that situation:
1. Use SQL Server Profiler, which is still available.
2. Use Extended Events feature.
3. Use SQL Server Alerts.

I will explain how to do the easiest "Deprecated Features" monitoring using SQL Server Alerts.

Prerequisites:
1. You have to have Database Mail set and tested on your SQL Server.
2. You have to have "Operator" set and tested on your SQL Server.

In order to understand how theses SQL Server Alerts work we will setup one via SSMS:
1. In SSMS connect to your test server expand "SQL Server Agent", right click on alerts and select "New Alert..."

2. In the New Alert window enter following information:
- Specify New Alert "Name";
- Change "Type" to "SQL Server Performance condition alert";
- Choose "Object" to "Deprecated Features";
- Choose "Counter" to "Usage" (only one available);
- Specify "Instance" of "Deprecated Feature" (in my case it is "DBCC SHOWCONTIG");
- Choose "Alert if counter" value as "rises above";
- The "Value" to rise above is unknown. By default it is "0", but it can be higher in case that event has been triggered since last server restart. That value can be determined by following script:

SELECT instance_name, cntr_value FROM msdb.dbo.sysalerts_performance_counters_view WHERE instance_name = 'DBCC SHOWCONTIG';

The number you get in "cntr_value" you have to specify as your new "Value" to trigger the alert.
In other word if any script on your server will run command "DBCC SHOWCONTIG" the value of "cntr_value" will increment. Then SQL Agent will compare that value with the value associated with this alert and if it "rises above" that "value" alert will be triggered and you will receive an email.
You will receive that email again and again until you disable an alert or change that checkup value.

3. On "Response" page check "Notify operators" box and check "E-mail" box for an appropriate operator who is supposed to receive an e-mail (For that purpose I've created "Test" operator).

4.  "Options" page is very important. At first you specify that alerting email will contain some kind of event description and also you can specify some additional description/note/directives you want to associate with that alert.
At second, you have to specify "Delay between responses". That parameter is crucial, it specifies how often you will get alerts after the event will fire. If you leave it as default "0" and event is triggered you will receive emails constantly. In order to prevent this specify delay as high as you can. The biggest delay you can set is 720 minutes and 59 seconds, which is little bit over of 12 hours.

5. After you done with setup click "OK" to set the alert.

Now you can fire the event by running following script on your server:

DBCC SHOWCONTIG

As the result you are supposed to receive following email:

DATE/TIME:      9/8/2014 11:42:58 AM DESCRIPTION:    The SQL Server performance counter 'Usage' (instance 'DBCC SHOWCONTIG') of object 'Deprecated Features' is now above the threshold of 4.00 (the current value is 5.00). COMMENT:        Please review your T-SQL code where that feature is used and replace it with corrected code. More details: http://msdn.microsoft.com/en-us/library/ms143729.aspx JOB RUN:        (None)

You can notice the line "(the current value is 5.00)" - that means if you want to stop notifications you have to modify "rises above value" for that alert (see step #2).

Looks simple isn't it?

The immediate problem is that number of all Deprecated Features is more than 240 for SQL Server 2012 and 250 for SQL Server 2014.
To list all these events use following script:

SELECT * FROM msdb.dbo.sysalerts_performance_counters_view WHERE [object_name] = 'Deprecated Features';

The second problem is that you will not be able to set alerts for following events in SSMS:
"'::' function calling syntax" and "Data types: text ntext or image".
The problem with them is that they have colon within the name. SSMS in SQL Servers 2012 and 2014 use view "msdb.dbo.sysalerts_performance_counters_view" while SQL Server uses "msdb.sys.dm_os_performance_counters" to get list of events. Stored procedure "sp_verify_performance_condition" was designed to verify alert parameters for 2008, but returns an error in 2012 and 2014. (For more details see http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1b744905-2005-4124-97c9-601c31221a8e/known-bug-in-msdbdbospverifyperformancecondition?forum=sqlnotificationservices)


Hopefully, if you are still reading, you understand the nature of SQL Alerts and how to use them to identify usage of "Deprecated Features".

Now I will give you easy script to setup all Deprecated Features' Event Alerts at once:
(PLEASE READ HOW TO USE THAT SCRIPT BEFORE RUNNING)

DECLARE @Operator SYSNAME = 'DBA'; SELECT 'EXEC msdb.dbo.sp_add_alert @name= N''' + CAST(SERVERPROPERTY('SERVERNAME') as NVARCHAR) + ' Alert - Use of Deprecated Feature: "' + REPLACE(RTRIM(instance_name),'''','''''') + '"'',' + CHAR(10)        + '@enabled=1,' + CHAR(10)        + '@delay_between_responses=43200,' + CHAR(10)        + '@include_event_description_in=1,' + CHAR(10)        + '@notification_message=N''' + 'Please review your T-SQL code where that feature is used and replace it with corrected code.' + CHAR(10)        + 'More details: http://msdn.microsoft.com/en-us/library/ms143729.aspx'',' + CHAR(10)        + '@performance_condition=N''' + RTRIM([object_name]) + '|' + RTRIM(Counter_Name) + '|' + REPLACE(RTRIM(instance_name),'''','''''') + '|>|'        + CAST(cntr_value as VARCHAR) + ''',' + CHAR(10)        + '@job_id=N''00000000-0000-0000-0000-000000000000'';' + CHAR(10)        + 'GO' + CHAR(10)        + 'EXEC msdb.dbo.sp_add_notification @alert_name=N''' + CAST(SERVERPROPERTY('SERVERNAME') as NVARCHAR)        + ' Alert - Use of Deprecated Feature: "' + REPLACE(RTRIM(instance_name),'''','''''') + '"'',' + CHAR(10)        + '@operator_name=N''' + @Operator + ''', @notification_method = 1' + CHAR(10)        + 'GO' AS Build_Alert_Script,        CASE instance_name WHEN 'String literals as column aliases' THEN 1 WHEN 'USER_ID' THEN 2 ELSE 0 END as EventOrder FROM sys.dm_os_performance_counters WHERE [object_name] like '%Deprecated Features%' and instance_name not in ( 'sp_trace_setevent', 'sp_trace_setfilter', 'sp_trace_create', 'sp_trace_setstatus' ) ORDER BY EventOrder, Build_Alert_Script;

Script usage:
0. Change value of "@Operator" variable by assigning yours "Operator" name.

1. Run script. Copy/Paste values (scripts) with EventOrder = 0 to a new SSMS window and run Make sure it. Make sure no errors were returned.

2. Wait for one minute and then run the script again. This time Copy/Paste values (scripts) with EventOrder = 1. That script is supposed to generate "String literals as column aliases" event. If you run it in the same batch with everything else that event will fire and you'd have to re-adjust it. Run copied script to generate the alert.

3. Wait for one minute and then run the script again. This time Copy/Paste values (scripts) with EventOrder = 2. That script is supposed to generate "USER_ID" event. The reasoning for a delay the same as for #2. Run that script to generate the alert.

Now you can go to "Alerts" and refresh the list. You have to see all newly generated events for your server.

Now you can test your alerts by firing any of "Deprecated Features". Just do not forget to re-adjust alert counters to prevent duplicate alerts after 12 hours.

NOTE: That script excludes "Deprecated Features" associated with tracing.That is done in order to avoid alerting in case of use of "SQL Server Profiler". Do not forget that whole that application will be deprecated in future versions on SQL Server.


Setting up alerts was an easy part. Now you have to monitor them and figure out which application uses them, but would say you are tired receiving these notifications you can simply delete all of them by one script. 

Here is a script to generate a code to delete all "Deprecated Features" alerts:

SELECT 'exec msdb.dbo.sp_delete_alert '''   + REPLACE(name,'''','''''') + '''; GO' FROM msdb.dbo.sysalerts WHERE name like '%Use of Deprecated Feature:%';

After running the produced code your test server will be clean from all alerts you've created by previous script.
I found it very useful when I did a research and troubleshooting.

Hopefully it will help you too.
More details about SQL Server "Deprecated Features" you can find here: http://msdn.microsoft.com/en-us/library/ms143729.aspx

------------- 2014-09-10
After couple of days having these alerts I've realized that several Deprecated Features are used by SQL Agent, SSMS and other tools. That means you really can't successfully monitor all alerts.


In order to list all Deprecated Features, which have been used since the last SQL Server restart, you can run following script:

SELECT * FROM sys.dm_os_performance_counters WHERE [object_name] like '%Deprecated Features%' and cntr_value > 0;