Today’s post is part of the series that I am running under the title "Under Appreciated Features of SQL Server". The series has been inspired by Andy Warren’s editorial on SQLServerCentral.com.

Microsoft SQL Server 2005 was a completely different SQL Server. The internals had changed and so had the mechanisms to monitor and manage the server. What this meant was that administration tasks had to be re-developed – all procedures had to be re-written and all script repositories had to be redone. Why? SQL Server 2005 made it easier for administrators to query the SQL Server meta-data by the introduction of Catalog views and Dynamic Management Views.

Catalog Views

In the days of SQL Server 2000, administrators had to query the system tables directly. This had a number of drawbacks due to the tight coupling with the underlying table structure. However, looking at it from a developer perspective, these tables are internal to the SQL Server and we as administrators were invading it’s privacy.

Hence, starting SQL Server 2005, any metadata information that was used by the SQL Server database engine is now exposed to the administrators via Catalog Views. They are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information.

You can read all about catalog views on Books On Line at: http://msdn.microsoft.com/en-us/library/ms174365.aspx.

If you are still stuck with SQL 2000 system table based queries

You can mover over to Catalog Views by using Compatibility Views (http://msdn.microsoft.com/en-us/library/ms187376.aspx). But, if you are now thinking of moving away from system tables, my suggestion would be to take the big jump and move over to Catalog Views directly.

A mapping of the system tables to the catalog views is available at http://msdn.microsoft.com/en-us/library/ms187997.aspx

Dynamic Management Views & Functions

Fondly abbreviated “DMV”, Dynamic Management Views are perhaps the most powerful feature of the SQL 2005+ line-up. No SQL Server release previously had the ability to allow the user to dynamically query the internals of the SQL Server while it was running. DMVs allow administrators to monitor the health of the SQL Server instance, diagnose problems and tune performance. If SQL Server were a human, DMVs would turn the skin transparent and allow you to see the pumping heart and the entire blood flow.

Contrary to older releases of SQL Server, they are not views based off the system tables, which would require that system tables are regularly updated and then queried, which would mean the introduction of performance issues. DMVs are such that they report off the underlying metadata, which make them faster and more accurate or “real-time”.

Dynamic Management Functions (DMF) are also available, which represent the internal state of the SQL Server as a function of the input provided. DMVs and DMFs are together known as Dynamic Management Objects.

Two types of Dynamic Management Objects are available to the users:

1. Server scoped dynamic management views, which require VIEW SERVER STATE permission on the server
2. Database scoped dynamic management views, which require VIEW DATABASE STATE permission on the database

Naming Conventions

Dynamic Management objects are always prefixed with dm_* and reside in the resource database (i.e. the sys schema).

DMVs can be used in T-SQL statements using two, three or four-part naming, just as is the case with a user table. DMFs, on the other hand can be referenced using two or three-part names. Dynamic Management objects cannot be referenced using a one-part name. (which means that you need to reference the DMV dm_exec_requests as sys.dm_exec_requests and not dm_exec_requests).

Securing Dynamic Management Objects

Dynamic Management Objects expose the internal state of any SQL Server. Therefore, they need to be controlled by user security and everyone cannot be provided access to them.

By default, in any Microsoft product, the paradigm is “secure by default”. Keeping this in mind, for SQL Server, the DENY would always take precedence. As discussed earlier, dynamic management objects need VIEW STATE permissions on the server and the database. Hence, all the administrators need to do is DENY the VIEW STATE permissions to wherever required (if server-level access is not required, DENY on the server, but allow on the database or, do the reverse if only server-level access is to be provided.

To know more about dynamic management objects, please visit:http://msdn.microsoft.com/en-us/library/ms188754.aspx

Where to find these in the Object Explorer

Dynamic Management Objects are, as mentioned, prefixed with the sys schema, indicating that they are members of the resource database. But, the resource database is hidden. Does this mean that the dynamic management objects are also hidden? No.

SSMS is a very powerful tool, and we use it so much that we almost take it for granted. The Object Explorer is exactly what it’s name suggests – allows you to explore all SQL Server objects – system or user defined.

Hence, to find dynamic management objects, all we need to do is to expand the master database in the Object Explorer and navigate out to the Views->System Views node.

Next, right click on the Views and filter the views listing for views belonging to the sys schema and objects containing dm_ in their name. To learn more about filtering objects in the Object Explorer, read my previous post in the Underappreciated Features series here.

Expand the filtered list and you can see all the DMVs listed for your convenience.

You can follow the same process to filter and look for the programmability components (dynamic management functions) in the master database.

Some key differences

It is important to understand that Catalog views are views based on the underlying system tables. Because data in tables is permanently persisted, the values in the catalog views are not reset upon server instance restart.

Dynamic objects, on the other hand report directly off the meta-data in memory, which means that they report data since the last SQL Server restart. This is a very important difference to keep in mind when working with catalog views and dynamic objects.

In Conclusion

Microsoft SQL Server, with each passing release is making increasing the richness and use of Dynamic Management objects and catalog views. I would request all readers to explore these powerful objects and make working with SQL Server a pleasurable experience.

Until we meet next time,

Be courteous. Drive responsibly.

Today’s post is part of the series that I am running under the title "Under Appreciated Features of SQL Server". The series has been inspired by Andy Warren’s editorial on SQLServerCentral.com.

How often have you faced a situation wherein while the host operating system is perfectly operational, but the SQL Server simply fails to respond to your connection requests? Starting SQL Server 2005, Microsoft provides a “reserve” dedicated administrator connection (DAC) – which is a special diagnostic connection for administrators when standard connections to the server are not possible.

Today, I will share with you some of the important points that I had captured when I first learnt about it in SQL Server 2005.

Connecting to Microsoft SQL Server using DAC

There can be only one connection to the SQL Server under dedicated administration – this is primarily to conserve the minimal resources which have been kept aside for these troubleshooting exercises. This means that it is always recommended to use DAC using the sqlcmd mode rather than the SQL Server Management Studio (where one may open up other connections accidentally – refer my post The multiple connections of SSMS to learn how).

Connecting via SQL Server Management Studio

In order to connect to a Microsoft SQL Server under the dedicated admin connection using SQL Server Management Studio, all that needs to be done is:

1. In the SQL Server Management Studio, click on “Database Engine Query”
2. Add the “ADMIN:” before the server name
3. Use the credentials of a user who is a member of the sysadmin fixed server role, and then click “Connect”

Connecting via SQLCMD

As I mentioned earlier, connecting using DAC is slightly tricky when using SQL Server Management Studio because the management studio can open multiple connections to the SQL Server if not used with care, and that would mean that our one DAC connection might be used up somewhere else accidentally.

A better alternate is to connect using DAC via the SQLCMD. To do so, the SQLCMD statement would look like:

sqlcmd  -S VPCW2K8DENALI -E -A -d master

The –A parameter indicates our intention to use the dedicated administrator connection.

What port does DAC use?

The reason an administrator would want to use DAC is because the SQL Server doesn’t respond to regular queries. Which also means that SQL Server is somehow not responding to the regular port that is used for communicating to SQL Server (typically, 1433). Therefore, DAC should have been implemented to use a different port. And that is exactly what has been done – with the additional security feature of the port changing every time SQL Server restarts.

Whenever Microsoft SQL Server restarts, an entry is added to the SQL Server error log indicating the port# in use by DAC until the next restart of Microsoft SQL Server.

Can I connect using DAC from anywhere?

Remote administration is a great thing, and mandatory in the days of virtualization and data centers. However, this also makes us all inclined to reject a feature if it cannot be used remotely. Similar is the case with DAC.

By default, DAC can only be used on the machine running the SQL Server instance. To run DAC from a remote machine, run the following advanced configuration option against your server:

/****************************************************
             !!!WARNING!!!
This script will modify the security configuration
of your SQL Server. Please inform your administrator
before use.

THIS SCRIPT IS SUPPLIED "AS-IS" AND WITHOUT
WARRANTY FOR DEMONSTRATION PURPOSES ONLY.
****************************************************/
--Enable display of advanced options
sp_configure 'show advanced options',1
RECONFIGURE
GO
--Turn ON Remote Admin Connections (DAC)
sp_configure 'remote admin connections',1
RECONFIGURE
GO
--Disable display of advanced options
sp_configure 'show advanced options',0
RECONFIGURE
GO

DAC and SQL Server Express Editions

To the best of my knowledge, DAC is not available by default on SQL Server Express Editions. Per Books On-Line (http://msdn.microsoft.com/en-us/library/ms188396.aspx) one can start SQL Server using trace flag 7806 (add –T7806 to the startup parameters) to enable DAC on express editions, but I have not given it a try myself.

Words of caution:

Dedicated Admin Connection, is a diagnostic and troubleshooting tool. It is not a security feature or replacement for administrative privileges, and hence must not be accessible to all. At the end of the day, it is a backdoor into the SQL Server instance, and therefore, must be used with extreme caution.

Also, it is to be kept in mind that while DAC allows you to recover from most scenarios, it may not work every time, especially, when the SQL Server is heavily constrained on the resources that can be spared for the DAC.

A good reason to have master as the default database

By the way, we may have a practice of assigning a user database as the default database for a login. If for some reason, this database is offline or not available, we will land up in an error – 4060. Because master is guaranteed to be available if the SQL Server instance is running, it is always recommended to have master as the default database for users belonging to the sysadmin role.

Until we meet next time,

Be courteous. Drive responsibly.

This is in continuation to my series on the Underappreciated features of Microsoft SQL Server. The series has been inspired from Andy Warren’s editorial on SQLServerCentral.com of the same name.

For any system that interfaces with other systems, bulk data transfer is a reality and has an importance that cannot be underestimated. Generally, the scenario is that data from multiple data sources (flat files being the most common) need to be imported into your SQL Server, or data from your SQL Server needs to be exported to these flat files.

Because Microsoft SQL server is naturally tuned to enterprise needs, it comes out of the box with a facility to “bulk insert” data into the SQL Server. Today, I will try to introduce you to the bcp command line utility and the BULK INSERT in SQL Server.

The bcp command line utility

The bcp command line utility copies data from a database in the Microsoft SQL Server to or from a data file – in a user-specified format (nothing can be more important than the last part, trust me).

BCP is a huge utility (huge in terms of the flexibility and functionality it provides), and you can read all about it on Books On Line at http://msdn.microsoft.com/en-us/library/ms162802.aspx. BCP has been written using the ODBC bulk copy API per the BOL page – http://msdn.microsoft.com/en-us/library/aa196743(SQL.80).aspx.

However, put quite simply, you can open up the command prompt on your machine running Microsoft SQL Server and run the following (use the name of your SQL Server instance for the –S parameter):

bcp "select * from AdventureWorks2008R2.HumanResources.Employee" queryout "E:Employee.txt" -c"" -S VPCW2K8DENALI -T -k

And you will see that the output file now has the all rows of the HumanResources.Employee table.

Open this Excel and choose to have default (tab) delimiters:

Madhivanan (blog) provides a great stored procedure to work with BCP. Do not forget to read about it from his post here (it’s one of my favourites).

BULK INSERT

BULK INSERT operations allow us to import data into a Microsoft SQL Server. This feature is a one-way traffic lane. The BULK INSERT operation is similar to the very familiar SELECT * INTO tableName FROM tableName2 query.

Pinal Dave (blog) demonstrates in his post (http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/), how to BULK INSERT the contents of a CSV file into a Microsoft SQL Server database.

The Books On Line page for BULK INSERT is http://msdn.microsoft.com/en-us/library/ms188365.aspx.From this page, we can summarize that:

1. INSERT and ADMINISTER BULK OPERATIONS permissions are required (unlike bcp). Also, ALTER TABLE may be required
2. The BULK INSERT statement can be executed within a user-defined transaction
3. The import may fail if executed on data of an invalid data type – this is change from previous versions of SQL Server. Also, this means that strings showing scientific notations are now considered invalid
4. BULK INSERTs may cause table level locks – a smaller batch size should be used if this is found to be the case

BULK INSERT does not support import of selective columns. Madhivanan explains how this can be done in his post here – http://beyondrelational.com/blogs/madhivanan/archive/2010/03/17/bulk-insert-to-table-with-specific-columns.aspx. He also summarizes some of the best practices in his post http://beyondrelational.com/blogs/madhivanan/archive/2010/12/22/best-practices-when-using-bulk-insert.aspx.

CAUTION

• By default, bulk copy operations do not fire triggers! You must specify the FIRE_TRIGGERS option for bulk copy operations to fire off INSERT and INSTEAD OF triggers. However, note that triggers are fired once for each batch in the operation. The INSERTED table passed to these triggers will have all the affected rows contained in the batch
• Specifying FIRE_TRIGGERS also causes bulk copy operations to be fully logged (thus, your transaction log may grow faster than expected) as opposed to being bulk-logged
• Result sets generating out of the bulk insert are not returned to the client

In Conclusion

Do you use bcp or BULK INSERT? If yes, do let me know – I would be interested in understanding the performance and/or other issues that you may have had to overcome.

This post ends the T-SQL enhancements section in the list of Underappreciated Features of SQL Server. From my next post, I will address some of the Administration Enhancements. If you would like me to write about something, do feel free to let me know.

Until we meet next time,

Be courteous. Drive responsibly.

v:* {behavior:url(#default#VML);}
o:* {behavior:url(#default#VML);}
w:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}

This is in continuation to my series on the Underappreciated features of Microsoft SQL Server. The series has been inspired from Andy Warren’s editorial on SQLServerCentral.com of the same name.

Today, we will look at a great new T-SQL enhancement introduced since SQL Server 2005 – the APPLY operator. Per Books On Line, “the APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.”

Instead of joining two tables, when APPLY is used, we join the output of a table-valued function with the outer table – such that the each row in the output of the table valued function is evaluated for each row of the outer table.

The two forms of APPLY

Just as we have multiple forms of the JOIN operator, we also have two forms of the APPLY operator – CROSS APPLY and OUTER APPLY. The difference is quite simple:

1. CROSS APPLY only returns rows from the outer table which produce a result set from the table valued function
2. OUTER APPLY returns both rows – irrespective of whether or not they produce a result set. NULL values are seen for the output of the table valued function for such rows

Examples

I believe the most common use of APPLY outside of a business application, is in performance tuning and database administration. One of the things that DBA are always monitoring is the answer to the question – “Which queries are currently running against a particular SQL Server?”. The simple query for this is:

SELECT * 
FROM sys.dm_exec_requests ser
CROSS APPLY sys.dm_exec_sql_text(ser.sql_handle)

As you can see, the query is such that the sql_handle is taken from the DMV – sys.dm_exec_requests and then applied to the function – sys.dm_exec_sql_text. Because we do not want NULL values, we used CROSS APPLY. As an exercise, try using OUTER APPLY and see what happens.

For a more examples, I would redirect the reader to Books On Line at: http://technet.microsoft.com/en-us/library/ms175156.aspx. The example and explanation is excellent, and very easy to understand.

The big difference – CROSS APPLY v/s CROSS JOIN

So, one might say that if the output of the table valued function was an actual table, CROSS APPLY can be replaced by a CROSS JOIN. However, that is not entirely true. CROSS JOIN will produce a Cartesian product, hence, if the outer table has m rows and the inner table n, the output will be (m x n) rows. CROSS APPLY, on the other hand, is more similar to an INNER JOIN.

Some things to keep in mind

Finally, let me draw your attention to a few things that you should keep in mind before using CROSS APPLY:

• It’s quite obvious that to use the APPLY operator, the compatibility level of the database must at least be 90
• Performance impact – It is quite clear that we will have at least one scan every time the TVF is executed. Hence, please keep an eye out on performance aspects before jumping in and using CROSS APPLY in everything – moderation is always good

Until we meet next time,

Be courteous. Drive responsibly.