Showing posts sorted by date for query dmv. Sort by relevance Show all posts
Showing posts sorted by date for query dmv. Sort by relevance Show all posts

Calculate SQL Azure Database Size

I was looking out for a correct way to programmatically determine the size of a SQL Azure database. After searching many solutions, I finally found one shared by Dimitri Furman of the SQL Server team.

His solution involves using the sys.database_files dmv and the FILEPROPERTY function with the ‘SpaceUsed’ argument.

To those new to sys.database_files, this system catalog view stores information and properties about each file for a database. Since it is a db-level view, it gives information about files in the current database only. Five properties that could be of interest are: logical filename, physical filename, initial size, maximum size and a growth increment.

To determine how much space is used in a file, you can use FILEPROPERTY with SpaceUsed.

Here’s an example: SELECT FILEPROPERTY(‘SomeFile’, ‘SpaceUsed’); I have often used the FILEPROPERTY function in the past while monitoring the progress of a SHRINK operation.

Query for calculating Size of a SQL Azure Database

Here’s how to combine sys.database_files with FILEPROPERTY to programmatically calculate the size of a SQL Azure database.

SELECT 
SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) 
AS DatabaseSizeInBytes,
SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.)/1024 /1024
AS DatabaseSizeInMB,
SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.)/1024/1024/1024 
AS DatabaseSizeInGB
FROM sys.database_files
WHERE type_desc = 'ROWS';

Let’s understand this query.

FILEPROPERTY() returns an int value for a file name stored within sys.database_files. Since sys.database_files is a db-level view, it gives information about files in the current database only. If a file is not present, null value is returned.

Since SpaceUsed represents "pages" and a page is 8 KB in SQL Server, so multiplying by 8192 gets the total bytes. Then dividing two times by 1024 converts the output to MB, and dividing by three times by 1024 converts the output to GB.

CAST is for casting the value to type bigint

If anybody is wondering about the dot (.) after an 8192, then it is to convert the result implicitly to a decimal value.

Please note that logs are excluded for the purposes of determining database size.

To know about Azure SQL Database resource limits, check https://docs.microsoft.com/en-in/azure/sql-database/sql-database-resource-limits

Does my SQL Server Database Support Compression?

Some time back, Praveen Dabade had written a nice article on SQL Server Compressed Tables and Indexes in SQL Server where he explained how compression is now supported on ‘ROW and PAGE’ for tables and indexes. However did you know that compression is an enterprise-level feature?

How do determine what Enterprise Edition features are enabled on your database? Well you can use the sys.dm_persisted_sku_features DMV to find what Enterprise Edition features are
enabled on your database.

Learn more about Dynamic Management Views (DMV’s) here

Here’s the query for the same

SELECT feature_name,feature_id
FROM
sys.dm_db_persisted_sku_features;


Running this query will list all edition-specific features that are enabled in the current database. Some of the database changing features restricted to the SQL Server Enterprise or Developer editions are Compression, Partitioning, ChangeCapture etc.

This DMV is also useful in situations where you are planning to move a database from a higher to a lower edition. Eg: From Enterprise to Standard edition. Running the query will tell you if there are any Enterprise Edition features enabled that may not work when you move to a lower edition.

The DMV will return no rows if no features restricted to a particular edition are used by the database.

Measure Transactions Per Second in SQL Server

Performance Monitor tool in SQL Server represents aspects of system performance, such as CPU Usage % , Memory Paging etc. The sys.dm_os_performance_counters Dynamic Management View exposes data of all performance counters for the particular instance of SQL Server. This view is extremely useful when you have to present the performance data of your database in your dashboard.

We have touched upon the usefulness of the sys.dm_os_performance_counters in my previous post Removing Deprecated Code and Future Proofing your Queries. In this post, we will see another use of the sys.dm_os_performance_counters view to measure SQL transactions per second for a database. Here’s a very handy query written by Joe Stefanelli

DECLARE @ctr bigint
SELECT @ctr = ctr
    FROM sys.dm_os_performance_counters
    WHERE counter_name = 'transactions/sec'
        AND object_name = 'SQLServer:Databases'
        AND instance_name = 'SomeDBName'
WAITFOR DELAY '00:00:01'
SELECT ctr - @ctr
    FROM sys.dm_os_performance_counters
    WHERE counter_name = 'transactions/sec'
        AND object_name = 'SQLServer:Databases'
        AND instance_name = 'SomeDBName'

As you can see, were taking the difference of two values for a delay of one second to get the number of transactions per second. In future posts, we will see some more queries using this useful DMV.

Find out Long Running query using sp_who2 with Dynamic Management Views‏ (DMV)

There are many ways to find out why your SQL server is performing slow. Suppose you know that your server is responding slowly while executing queries and you want to find out the query that takes a lot of time to execute. If you want to find out slow queries, you can use dynamic management views from version 2005 onwards. However you can still use the old system stored procedure sp_who2 along with management views, to find this info.

Run the following code

EXEC sp_who2

and see the result shown below. Find out rows with runnable status with highest CPUTime.

long_run1

Sp_who2 will not display the text of the query, so we need to find out the text using dm_exec_requests and dm_exec_sql_text management views. The view dm_exec_requests will show the text of the query. All we need to do is to find out spid from the result of sp_who2 and filter it in dm_exec_requests using session_id column. The following code displays the text of the query.

SELECT
        sql.text AS statement_text
FROM
        sys.dm_exec_requests  AS req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as sql
WHERE
    req.session_id=52

Similarly you can also use dynamic management views to find out various root causes for performance issues.

Find Last Run Query in SQL Server

Have you ever wondered what SQL query was last executed by your users across all SQL Server databases on your server? I have seen some solutions on the internet that use the sysprocesses view to retrieve this information. In this post, I will show you how this information can be retrieved better using Dynamic Management Views.

Please use this query:

SELECT conn.session_id, sson.host_name, sson.login_name, 
 sqltxt.text, sson.login_time,  sson.status
FROM sys.dm_exec_connections conn
INNER JOIN sys.dm_exec_sessions sson 
ON conn.session_id = sson.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS sqltxt
ORDER BY conn.session_id

Here I have utilized the sys.dm_exec_connections Dynamic Management View, in conjunction with the sys.dm_exec_sessions DMV and sys.dm_exec_sql_text Dynamic Management Function (DMF) to return the last query executed against all SQL Server databases, in that server.

Here’s a quick overview of what these DMV’s and DMF do
sys.dm_exec_connections - Returns information about the connections established to this instance of SQL Server and the details of each connection

sys.dm_exec_sessions - Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more

sys.dm_exec_sql_text - Returns the text of the SQL batch that is identified by the specified sql_handle

In the last statement, we are passing the value contained in the most_recent_sql_handle column of this DMV to the sys.dm_exec_sql_text DMF.  The DMF returns the text of the sql query, whose sql_handle we passed to it.  This sql_handle that we passed, uniquely identifies the query.

Here’s the output

sql-last-run-query

SQL Server: Removing Deprecated Code and Future Proofing your Queries

New features are added with every release of SQL Server and as a result, some features get removed or deprecated. Deprecated features are features that are still operational (for backward compatibility) but will be removed in a future version. Deprecated features can be of two types: those that will be deprecated in a future version and those that will be deprecated in the next version.

In this article, we will explore how to track deprecated code and correct it. I will also share our observations when we performed the same operation on a live production database. Please feel free to retweet and share this link with fellow developers.

Tracking Deprecated Code in your Database

Tracking deprecated features can help you identify potential future bugs and upgrade and compatibility problems in your database. SQL Server 2008 provides the following methods to detect deprecated code in your database:
  • SQL Server: Deprecated Features performance counter
  • Deprecation category of trace events that includes the Deprecation Announcement and Deprecation Final Support event classes, which can be monitored using SQL Server Profiler or SQL Trace. You can use event notification and track these events via a stored procedure.
  • sqlserver.deprecation_announcement and the sqlserver.deprecation_final_support Extended events
  • Performance Monitor Tool (perfmon.exe) to display the value of counters for SQL Server: deprecated features object.
  • SQL Trace Collector to automate the collection of deprecation events
We will discuss the SQL Server: Deprecated Features performance counter  and PerfMon tool in this article.

Observe the following code which uses the Deprecated Feature performance counter and gives a feel of how the deprecation feature works:

sql deprecated features

OUTPUT

SQL Deprecated Code

The sys.dm_os_performance_counters dynamic management view (DMV) maintains a count every time a deprecated feature is used. The code shown above is for a named instance MSSQL$<instancename>. For a SQL Server default instance, just replace the above code with this one

SELECT
object_name, instance_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Deprecated Features'
AND cntr_value > 0;


Another way of using this performance counter is by using PerfMon tool (Start > Run > Perfmon) on your machine. For example lets add the sp_dboption counter from the SQLServer:Deprecated Features performance object as shown below.

image

To test the counter, fire the following statement in SSMS which uses the deprecated feature sp_dboption

USE master
EXEC sp_dboption 'Umbra', 'read only', 'FALSE'
GO

image

and note the deviation and counter value when the query is fired. Microsoft recommends to use ALTER DATABASE instead of sp_dboption.

ALTER DATABASE
SET READ_ONLY
GO


It’s always best to log and save your results and the SQL Profiler gives you options to do that.

Note: If you are upgrading your SQL Server 2000 installation to SQL Server 2008, then remember to specifically search for the features that were deprecated in SQL Server 2005, since those features may no more exist in SQL Server 2008. Google/Bing is your friend here and use them to search for removed features. Another way is to look at the Backwards Compatibility section in the SQL server 2005 Books Online Installation guide and remove the deprecated features listed in that section.

What to do once Deprecated Code is detected?

Microsoft has created a page that describes all the deprecated features and has listed alternative techniques to future-proof your code. Although in the beginning of this article, I have shown only a partial screenshot of the deprecated features used in a live production database, we actually encountered over 50 deprecated features that we were using. I have attempted to list some important ones randomly and hope that this list will be useful to you too and would save you time if you were to deal with deprecated code in your database.

Here’s a list of our observations:
  • The COMPUTE or COMPUTE BY syntax is deprecated since it created a new summarized result set which was difficult for apps to consume. Instead now use GROUP BY with ROLLUP which occurs once per compilation.
  • While using locks in SQL Server 2008, although sp_lock and sys.syslockinfo are still available, their use has been deprecated and instead SQL Server favors the Sys.dm_tran_locks.
  • While performing locks use the rowversion data type instead of the timestamp data type
  • The text, ntext, and image data types are being deprecated (since SQL 2005) in favor of varchar(max), nvarchar(max) and varbinary(max) data types.
  • Instead of sp_change_users_login, use ALTER USER WITH LOGIN
  • Compatibility levels 60, 65, and 70 are deprecated and support is provided only for compatibility levels 80 and higher
  • DBCC DBREINDEX has been deprecated and replaced with the ALTER INDEX REBUILD command and DBCC INDEXDEFRAG has been replaced with ALTER INDEX REORGANIZE
  • The sp_depends system stored procedure and the sys.sql_dependencies catalog view have been deprecated. Use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead
  • Use of WRITETEXT or UPDATETEXT is deprecated when modifying text, ntext, or image data types.
  • In the CREATE FULLTEXT CATALOG arguments, rootpath has been deprecated.
  • Full-Text Search stored procedures are deprecated. Use CREATE FULLTEXT CATALOG, ALTER FULLTEXT CATALOG, and DROP FULLTEXT CATALOG instead
  • If you are using XML, XDR (XML Data Reduced) schemas is deprecated
  • SET SHOWPLAN_ALL and SET SHOWPLAN_TEXT options are deprecated and replaced with the SET SHOWPLAN_XML option
  • SET STATISTICS PROFILE option has been deprecated and replaced with the SET STATISTICS XML option
  • The old syntax of RAISERROR syntax specifying the error number and message number is deprecated (RAISEERROR 50005 ‘Exception Occurred’). Instead use the new syntax RAISEERROR(50005, 10, 1) which allows you to specify the messageid, severity and state)
  • In earlier editions owner.tablename was supported. In SQL 2008, this is deprecated. If an object belongs to a schema, then it must be referenced with its associated schema name so the syntax changes to schemaname.tablename.
  • The sp_attach_db stored procedure has been deprecated and you should use CREATE DATABASE with FOR ATTACH clause
  • sp_addlogin is replaced with CREATE LOGIN. sp_defaultdb, sp_defaultlanguage, sp_password are all replaced with ALTER LOGIN
  • sp_droplogin and sp_revokelogin is replaced with DROP LOGIN
  • sp_adduser, sp_dropuser, sp_grantdbaccess, and sp_revokedbaccess are replaced with CREATE USER, DROP USER and ALTER USER
  • sp_addrole and sp_droprole are replaced with CREATE ROLE and DROP ROLE
  • The ALL clause has been deprecated.
  • Log shipping is deprecated and replaced with Database Mirroring
  • The SQL Server Surface Area Configuration tool is deprecated and replaced with Policy-Based Management.
  • Remote servers will soon be deprecated and you should no longer use sp_addserver, sp_addremotelogin, sp_remoteoption and sp_helpremotelogin. Use Linked server instead.
  • DUMP and LOAD have been replaced with BACKUP and RESTORE
  • sp_helpdevice is replaced by sys.backupdevices catalog view
  • The FASTFIRSTROW table hint is deprecated
  • String literals as column aliases – Microsoft discourages the use of string as a column alias in a SELECT statement as it occurs per compilation. So instead of 'RetValue'= @ret_val, you should use @ret_val as ‘RetValue’
These were just some of the deprecated features we encountered and shared with you. For a detailed list, check this documentation.

Here’s a mantra - Rewrite any pre-written code that uses deprecated features, and avoid using any deprecated features in new development! It will make your life as a database guy, easier.

SQL Server: Display Query Execution Plans–Different ways

A SQL Server Query execution plan can return detailed information about how the statements are executed. Execution Plans can be viewed in different ways and formats. In this article, we will discuss a couple of options using which you can view the execution plan of a query.

Method 1: Using SQL Server Management Studio (SSMS)

Just highlight a piece of code in SSMS, right click and select ‘Display Estimated Execution Plan’

display estimated execution plan

OUTPUT

display execution plan

Method 2: USE SET statements

You can use SET statements with options like showplan_text, showplan_all, showplan_xml

showplan_text

showplan_text

OUTPUT

showplan_text

showplan_all

Similarly the SHOWPLAN_ALL option displays the same query plan as the SHOWPLAN_TEXT option, but it also provides additional columns of output, for each row of textual output

SET SHOWPLAN_ALL ON;
GO
SELECT *
FROM Production.Product
WHERE ProductID = 526;
GO
SET SHOWPLAN_ALL OFF;
GO


showplan_all

showplan_xml

To view the results in an XML format, use SHOWPLAN_XML

SET SHOWPLAN_XML ON;
GO
SELECT *
FROM Production.Product
WHERE ProductID = 526;
GO
SET SHOWPLAN_XML OFF;
GO


showplan_xml

The result can be huge, so just right click the result > copy and paste in a notepad or XML editor to view the results.

Method 3: Use Plan Cache

You can also use the plan cache to find the execution plan of a query. Use the dm_exec_cached_plans DMV which returns a row for each query plan that is cached by SQL Server for faster query execution.

USE master;
GO
SELECT *
FROM
sys.dm_exec_cached_plans AS cp
CROSS APPLY
sys.dm_exec_text_query_plan(cp.plan_handle, DEFAULT, DEFAULT);
GO


The query above retrieves the plan handles of all query plans in the cache by querying the sys.dm_exec_cached_plans dynamic management view.

sql plan cache

Every .NET Developer Should Know About the Database they are working with

I am a strong believer that a Developer should understand the databases and network he/she is interacting with. As a .NET Developer, having TSQL and SQL Server Administration knowledge to some degree of depth, really helps to design and develop your applications as well as communicate with the DBAs and admins you work with. A lot of us have testing and staging environments and being able to manage those environments, saves the organization on some resources.

Also with the economies changing rapidly, many developer positions require you to have knowledge about querying and managing databases. Flexibility and the ability to learn and understand the application and it’s components differentiates a good developer from the others and keeps you ahead in the race. Although BI is an essential part of an application, in this article, I will focus on the administration of a database.

In this article, I have attempted to list some articles we have written in the past that will give you information about handling a SQL Server 2005/2008 database. Hope you like them!

Please retweet and share this post with fellow developers. Thanks!


Identifying SQL Server Bottlenecks and Performance Issues

SQL Server: Identify Memory and Performance Issues in T-SQL Queries and Fix them - Sometimes you may notice that some T-SQL queries are taking too much time to execute and thus slowing down the performance of SQL Server and other applications. You can find and rectify these queries using the following methods shown in this article

Find the Most Time Consuming Code in your SQL Server Database - This post will demonstrate how to find T-SQL code (SQL Server 2005/2008) that takes the most time to execute.

Monitor Running Processes in SQL Server 2005/2008 - You often need to monitor the processes running on the server in order to improve the performance, by tuning them. A common option is to make use of a profiler. But in SQL Server versions starting 2005, we can also use dynamic management views.

SQL Server: Monitor Long SQL Agent Jobs - In SQL Server, there are various jobs running on the server, each solving a different purpose. Sometimes these jobs run for a long time. The best way to identify and monitor these long running jobs is to make use of a profiler.

Important DMVs to monitor CPU – SQL Server - Dynamic Management Views (DMVs) can be very useful to diagnose and troubleshoot common performance problems in SQL Server. Here are some important Dynamic Management Views (DMV’s) to monitor the CPU where SQL Server is installed.

Monitor Activities on your SQL Server 2005/2008 - Have you heard about or used SQL Server MVP Adam Mechanics Who is Active This is a must have script for DBA’s and if you have been using sp_who or sp_who2, then you will love it.

List of Database Engine Error Messages - All system and user-defined error messages in an instance of the Database Engine are contained in the sys.messages catalog view. Here’s how to query this catalog view to list the Error Messages based on their severity

Moving Data in SQL Server

Move Data to a Different Table using OUTPUT clause – Shows how to move some data from one table to another table and then delete the data from source table.

SQL Server: Transfer Objects From One Schema to Another - This post describes how to transfer objects from one schema to another

Transfer Logins from SQL Server 2005 to SQL Server 2008 - When you want to move a database from one server to another, you can take a backup of the database and restore into the other server. However this will not transfers the logins from one SQL server to another, say SQL Server 2005 to SQL Server 2008. This post shows the right way to do it.

SQL Server: Export Table to CSV - Exporting data from SQL Server to a .csv file is often needed to use that data into a different system. There are two easy ways to do this – using BCP and using SQL Server Management Studio.

Load Comma Delimited file (csv) in SQL Server - We often need to import data that comes from different data sources, into a database like SQL Server. Usually data comes in the form of a comma delimited file aka CSV file.

SQL Server Migration Assistant v5.0 - Microsoft recently announced the release of SQL Server Migration Assistant (SSMA) v5.0, a tool to further simplify the user experience in automating the migration of Oracle, Sybase, MySQL and Microsoft Access databases to SQL server or SQL Azure

SQL Server: Move Table to a new File Group - If a database is expected to grow rapidly, it becomes challenging to maintain the data files in the same location/drive. To manage disk space well, it one solution to this problem is to identify large tables and accordingly move new data to a new File group, which can be mapped to a different drive path. This article shows how to move a SQL Server Table from one File group to another.

Know your SQL Server Objects and Manipulate them

SQL Server Management Objects 2008 (SMO) New Features - In this article we will practically explore some features of SQL Server Management Objects.

Filtered Index Vs Indexed Views in SQL Server 2008 - In this article I have listed some differences between Filtered Index and Indexed View that will help understand the differences between the two.

View Index MetaData – SQL Server - The sp_helpindex stored procedure gives useful information like name, description and key about the indexes on a table or view. However for retrieving detailed information, you should make use of the sys.indexes object catalog view.

Change an Existing Index in SQL Server - With SQL Server, you can use the DROP_EXISTING clause to rebuild the index, add or drop columns, modify options, modify column sort order, or change the partition scheme or filegroup. Let us see how to rebuild the index and then add a column to an existing index

Find the Most Used Stored Procedures in SQL Server – This post uses the sys.dm_exec_query_stats DMV to return the 3 most used stored procedures in your SQL Server database

Useful SQL Server System Stored Procedures You Should Know - System Stored Procedures are useful in performing administrative and informational activities in SQL Server. Here’s a bunch of System Stored Procedures that are used on a frequent basis (in no particular order).

Find the Missing Identity Numbers in SQL Server 2005/2008 - We as Database Developers often come across tables that have identity columns. Many a times, there are missing rows in that table, which have probably been deleted during a database operation. Have you wondered how to find the missing rows?

Find Out The Parameters Of A Stored Procedure in SQL Server 2005/2008 - Recently while working on an application that heavily uses Stored Procedures, I had to often open up SQL Server Management Studio/ Visual Studio Server Explorer to physically check the parameters of a Stored Procedure. This became cumbersome at times since the procs were in different databases and I wish there were a query that returned me the Stored Procedure parameters, as soon as I supply it with the stored procedure name. If you have been looking out for something similar, here’s what I came up with

Identify Tables that have Clustered index but no NonClustered indexes - I was recently helping a client determine tables in his SQL Server 2005/2008 which had Clustered Index but did not have any nonclustered index on them


SQL Server: Disable Table Constraints (all or some) - Constraints let you define a way to automatically enforce the integrity of a database. A table constraint is declared independently from a column and can be applied to more than one column in a table. Sometimes you may need to disable one or all table constraints, in order to import data, truncate tables etc.

SQL Server: Count Rows in Tables and its Size - In this post we will see how to count rows in all the tables of a database using SQL Server.

View Object Dependencies in SQL Server 2008 - Viewing object dependencies within a database as well as between databases and servers has become easier in SQL Server 2008. SQL Server 2008 introduces a catalog view (sys.sql_expression_dependencies) and dynamic management functions (sys.dm_sql_referenced_entities & sys.dm_sql_referencing_entities) that can help in dependency
tracking.

Rename a SQL Server database - Sometimes we may need to rename a SQL Server database for testing purposes or when a testing database goes online, we may need to rename it on a production database. This article shows how.

SQL CLR Stored Procedure using Visual Studio 2010 - In this post, we will see how to use SQL CLR functionality for defining Stored Procedure using C# in VS 2010. We will also see how to enable CLR under SQL Server.

List all Default Values in a SQL Server Database - I had earlier written a query to Find a Column Default value using T-SQL. Here’s how to find the default value of all columns in all tables of a database

Find Column Default Value using T-SQL – SQL Server - Here’s how to find the default value of a Column using T-SQL.

Find Unused Objects in your SQL Server Database - Here are a couple of options that you can use to find unused objects in your database

SQL Server: Calculate tempdb Space using DMV - In this article, we will see how to calculate total and free space in tempdb using the sys.dm_db_file_space_usage DMV, which returns space usage information for each file in the database.

List of Undocumented Stored Procedures in SQL Server - I have been searching for a list of Undocumented Stored Procedures in SQL Server for quite a while. Here is a list collected from forums and other sites for your reference.

List down the DataType and Size Of all Columns of All Tables in a SQL Server 2005/2008 Database - While programming a Data Layer, I often refer to the database and its tables for the datatypes and its sizes. It’s real handy to be able to print a list of the properties of the columns. Here’s a query that will help you fetch the DataType and Size Of all Columns of All Tables in a SQL Server 2005/2008 Database

Display List of All Databases with their Sizes in SQL Server 2005/2008 - Here’s a query that lists the properties of a database using the sys.master_files and sys.databases, both System Views for retrieving Database information.

Find Out Tables in a Database Having Identity Columns - The simplest way I use to find out the tables in SQL Server 2005/2008 having Identity Columns is to use the query over here

T-SQL, SQL Administration Articles Link List – July 2011

Here’s a quick wrap up of the articles published on SQLServerCurry.com in the month of July 2011

SQL Server Administration Articles

SQL Server: Top 10 Cached Queries - We have covered Dynamic Management views (DMV’s) many times earlier. In this article, we will see how to view the most cached statements on the server.

SQL Server Bit Data Type and how it Stores Values - SQL Server supports the bit datatype which can be used to store flag values like 'true' or 'false'. However it should be noted how the values are stored in the bit column. This article gives you an overview of the same.

SQL Server 2008 R2 SP1 and Denali CTP 3 Released - Download links - Microsoft recently released the first service pack of SQL Server 2008 R2 for it’s RTM as well as Express Edition.

SQL Server Migration Assistant for Oracle v5.1 - Microsoft SQL Server Migration Assistant (SSMA) for Oracle is a tool to automate migration from Oracle database to SQL Server. Microsoft recently released an update to this tool

T-SQL Articles

SQL Server 2008: Divide Time Column with Another Column - Here’s a simple example that divides a SQL Server 2008 Time column with another to calculate the time taken per employee to finish the task.

SQL Server: DateTime vs DateTime2 -  The datetime2 datatype was introduced in SQL Server 2008 along with the date and time datatypes. Unlike the datetime datatype in SQL Server, the datetime2 datatype can store time value down to microseconds and avoids the 3/1000 second rounding issue.

SQL Server: Common mistake while Calculating Quarter Sales - When it comes to calculating quarter sales, I have always seen developers grouping only by quarter which is incorrect.

SQL Server: Distinct Count across Multiple Tables - One of my clients had a SQL Server database with an email column repeated across multiple tables. He wanted two queries – all email’s that are not repeated across the table, i.e. distinct ones as well as a count of the email addresses across multiple tables in the database.

SQL Server: @@RowCount and Zero Count - The SQL Server system function @@rowcount is used to return the number of rows affected by the last executed statement. There are atleast three cases where @@rowcount will return the value 0. Let us explore these cases in this post.

SQL Server: Find Current Language of the Server - Suppose you are working in an environment in which your server supports various languages and you want to find out what is the current language of server.

SQL Server: Behavioral Change in Union Operator - There is a behavioral change in the SQL Server UNION operator from version starting from 2005.

SQL BI Articles

SSIS: What’s New in SQL Server Denali - I was looking for an introduction to what’s new in SQL Server Integration Services (SSIS) in Denali, the next version of SQL Server. I stumbled upon a nice video which gives an overview of the new features of SSIS in the next CTP of Denali

SQL Server Denali Videos - After my last post on What’s New in SSIS: SQL Server Denali, I got some requests via twitter to list videos that cover more on SQL Server Denali. Here are some videos I like, that were recorded during Tech-Ed and other tech events:

Other Articles

Microsoft MVP Award – 4 Years in a Row - I was awarded the Microsoft MVP title again for the 4th consecutive year, and I thank Microsoft for bestowing this award on me.

SQL Server: Top 10 Cached Queries

We have covered Dynamic Management views (DMV’s) many times earlier. In this article, we will see how to view the most cached statements on the server.

The view sys.dm_exec_cached_plans and function dm_exec_sql_text can be used for this purpose.

Let us execute the following query

sql-server-dmv-cache
select top 10
    db_name(sql_text.dbid) as db_name,
    cache_plan.objtype,
    sql_text.text ,
    cache_plan.usecounts
from
    sys.dm_exec_cached_plans as cache_plan
    cross apply sys.dm_exec_sql_text(plan_handle) as sql_text
order by
    usecounts desc


Here are some important points to note about this query:
  • The result is based on the descending order of the column ‘usecounts’ which is used to indicate the number of times the cached objects are used.
  • The column ‘object type’ indicates whether the statement is of the type procedure,view, adhoc etc.
  • The column text shows the exact statement which was cached.
  • The column db_name is null for the cached types adhoc, prepared, etc
OUTPUT

sql-server-dmv-cache2

T-SQL, SQL Administration and SQL Server BI Articles Link List – May 2011

Here’s a quick wrap up of the articles published on SQLServerCurry.com in the month of May 2011

SQL Server Administration Articles

FileStream in SQL Server 2008 - In this article, we will see how to work with FileStream in SQL Server 2008 for storing unstructured data like documents and images on file system

SQL Server Monitoring Management Pack - Microsoft has released the SQL Server Management Pack for for Operations Manager 2007 SP1 and R2. This pack is used to discover SQL Server 2005, 2008, and 2008 R2 and monitors SQL Server components such as database engine instances, databases, SQL Server agents, jobs and other roles such as Reporting Services, Analysis Services, Integrations Services

Compressed Tables and Indexes in SQL Server 2008 - In this article, we will see how to use the new feature of compressing Tables and Indexes in SQL Server 2008 with some examples

SQL Server Migration Assistant v5.0 - Microsoft recently announced the release of SQL Server Migration Assistant (SSMA) v5.0, a tool to further simplify the user experience in automating the migration of Oracle, Sybase, MySQL and Microsoft Access databases to SQL server or SQL Azure

Troubleshoot Deadlocks using SQL Server Profiler 2005/2008 - In this article, we will see how to capture deadlocks while modifying data using SQL Server Profiler in SQL Server 2005/2008

SQL Server: Calculate tempdb Space using DMV - I am a huge fan of Dynamic Management Views (DMV) as they provide me the ability to look into the internals of SQL Server. I have been writing regularly about the same. Here are some of the articles

DDL Triggers in SQL Server 2005 and 2008 - In this post, we will see how to implement DDL triggers in SQL Server 2005 and 2008. 

T-SQL Articles

FileStream in SQL Server 2008 - In this article, we will see how to work with FileStream in SQL Server 2008 for storing unstructured data like documents and images on file system

how to use FLWOR Expression designed for querying XML data in SQL Server 2005 and 2008.

SQL Queries – beyond TRUE and FALSE - Most of the SQL novices are more accustomed to thinking in terms of two-valued logic (TRUE, FALSE) in SQL. But SQL uses three-valued logic – TRUE, FALSE and UNKNOWN. It means that the value of an expression may be TRUE, FALSE or UNKNOWN. Confused?

SQL CLR Stored Procedure using Visual Studio 2010 - In this post, we will see how to use SQL CLR functionality for defining Stored Procedure using C# in VS 2010. We will also see how to enable CLR under SQL Server

Troubleshoot Deadlocks using SQL Server Profiler 2005/2008 - In this article, we will see how to capture deadlocks while modifying data using SQL Server Profiler in SQL Server 2005/2008

SQL Server–Error Handling using Try Catch Block - In this post, we will see how to handle errors in SQL Server 2005 and 2008. In SQL Server 2005, Microsoft has introduced a new construct to handle errors in SQL Server that is ‘TRY – CATCH’. It is similar to .NET ‘Try – Catch’ block for handling the exceptions

SQL Server CLR User Defined Function using Visual Studio 2010 - In this post, we will see how to create a User Defined Function using Visual Studio 2010

SQL BI Articles

SSRS 2008: Cascading Parameterized Report - In this article, we will see how to create a SSRS 2008 report with cascading parameters. We will create cascading parameters and add those to our report. For this demonstration, we will use SQL Server 2008 database ‘AdventureWorks’ for designing the report as well as parameters.

SQL Server: Calculate tempdb Space using DMV

I am a huge fan of Dynamic Management Views (DMV) as they provide me the ability to look into the internals of SQL Server. I have been writing regularly about the same. Here are some of the articles

List Dynamic Management Views (DMV) by Category in SQL Server 2008
Monitor Running Processes in SQL Server 2005/2008 (written by Madhivanan)
SQL Server: Identify Memory and Performance Issues in T-SQL Queries and Fix them (written by Madhivanan)
Find the Most Used Stored Procedures in SQL Server
Dynamic Management Views (DMV) for SQL Server Performance and Tuning
Important DMVs to monitor CPU – SQL Server

In this article, we will see how to calculate total and free space in tempdb using the sys.dm_db_file_space_usage DMV, which returns space usage information for each file in the database. tempdb is an important object in SQL Server as all databases utilize its resources. Thus it becomes important to monitor tempdb’s space usage.
Here’s the query to find the Total tempdb Size and Free space
TempDB size DMV

SELECT
SUM(unallocated_extent_page_count
+ user_object_reserved_page_count
+ internal_object_reserved_page_count
+ mixed_extent_page_count
+ version_store_reserved_page_count) * (8.0/1024.0)
AS [TotalTempDBSizeInMB]
, SUM(unallocated_extent_page_count * (8.0/1024.0))
AS [FreeTempDBSpaceInMB]
FROM sys.dm_db_file_space_usage


In the query above, we are doing a sum of columns across all tempdb files. The result is multiplied by 8 as page count values will always be a multiple of eight. The division by 1024 is to calculate the size in MB’s.

OUTPUT

image

Monitor Running Processes in SQL Server 2005/2008

You often need to monitor the processes running on the server in order to improve the performance, by tuning them.  A common option is to make use of a profiler. But in SQL Server versions starting 2005, we can also use dynamic management views.

select
    source_code,stats.total_elapsed_time/1000000 as seconds,last_execution_time
from sys.dm_exec_query_stats as stats
    cross apply(SELECT text as source_code FROM sys.dm_exec_sql_text(sql_handle))AS query_text
order by total_elapsed_time desc


The above code will list out the queries based on the descending order of the time they take

monitor2

The sys.dm_exec_query_stats is a Dynamic Management view that gives the statistical
information's about cached data. The sys.dm_exec_sql_text is the another view that
gives actual text of the sql_handle which is in binary format. It converts the binary
to actual characters

You can also make use of an article from the Microsoft available at
http://msdn.microsoft.com/en-us/library/ms191511.aspx

Further Reading:

Dynamic Management Views (DMV) for SQL Server Performance and Tuning
Important DMVs to monitor CPU – SQL Server

SQL Server: Identify Memory and Performance Issues in T-SQL Queries and Fix them

Sometimes you may notice that some T-SQL queries are taking too much time to execute and thus slowing down the performance of SQL Server and other applications. You can find and rectify these queries using the following methods:

Method 1 : Use Dynamic Management View

sqlmemoryissues

SELECT
txt.text, total_elapsed_time
FROM
sys.dm_exec_query_stats stat
CROSS APPLY sys.dm_exec_sql_text (stat.sql_handle) txt
ORDER BY
total_elapsed_time desc

The view sys.dm_exec_query_stats will have statistical information of the cached queries
The view sys.dm_exec_sql_text will show the actual query executed. The output will show the results based on the time, the query takes to run, which you can identify and improve upon.

memory2

Method 2 : Use SQL profiler

Sometime a query may have code that runs for ever. In such cases, the query never seems to complete execution. You can identify such queries using a SQL profiler.

For eg: Run this code

while 1=1
print 1

memory4

The above code will print 1 for ever, thus consuming too much memory. To identify these queries, run a SQL profiler and see the result. As you notice the column CPU, Reads, Writes and Duration will be NULL for that code. To rectify, you can stop that code to release the memory.

You may also find the following posts helpful

Find the Most Time Consuming Code in your SQL Server Database

List Dynamic Management Views (DMV) by Category in SQL Server 2008

T-SQL and SQL Server Administration Articles Link List – January 2011

Here’s a quick wrap up of the articles published on www.SQLServerCurry.com in the month of January 2011

T-SQL Articles

Select TOP With TIES in SQL Server - The SQL Server TOP Expression returns only the first set of rows that is either a number or a percent of rows. If you want additional rows to be returned from the base result set if duplicate values exists in the qualified set of results, then use TOP..WITH TIES as shown in this post

Create Date in T-SQL - SQL Server - Here’s a very simple query that shows how to create a Date using T-SQL if you are given the Day, Month and Year

Rollback Transaction in SQL Server - In this article, I have explained how to use a Try..Catch block to commit and rollback transaction

Rollback Nested Transactions in Stored Procedure - SQL Server - In this article, we will use an example to see how to rollback nested transactions in Stored Procedures

MINUS Keyword in SQL Server – Alternatives - MINUS operator (in oracle) is used to subtract the rows which are available in the second result, from the first result set. SQL Server does not have a built-in MINUS keyword, but in SQL Server, it’s equivalent is the EXCEPT operator or using NOT EXISTS

Date Difference in SQL Server in Days, Hours, Minutes and Seconds - A lot of users often ask questions related to finding difference between two date. The results are expected in Days, Hours, Minute and Seconds. Here’s a query that shows how to find the difference between two dates in SQL Server

UNPIVOT example in SQL Server - The PIVOT operator allows you to rotate, or pivot, data between columns and rows, and performs aggregations where they are required on any remaining column values. The UNPIVOT operator performs the reverse operation of PIVOT, by rotating columns into rows.

SQL Server Administration Articles

Backup Compression in SQL Server 2008 - Backup Compression is a new database administration feature in SQL Server 2008 and higher which allows you to compress your database backup. In this article, I have listed some frequently asked questions (FAQ) about Backup Compression in SQL Server 2008.

Deny Delete on a Table to All Users Except a Few – SQL Server - I was speaking to a DBA friend of mine the other day on how do they Deny deletes on a table to all users, including admins. One of the solutions was to use a trigger as shown in this post

Find the Most Used Stored Procedures in SQL Server - Let us see how to use the sys.dm_exec_query_stats DMV to return the 3 most used stored procedures in your SQL Server database

Load Comma Delimited file (csv) in SQL Server - We often need to import data that comes from different data sources, into a database like SQL Server. Usually data comes in the form of a comma delimited file aka CSV file. These are the two easy ways to import data from a CSV file into a table of a SQL Server Database – Using Bulk Insert and Using SQL Server Management Studio.

SQL Server: Export Table to CSV - Exporting data from SQL Server to a .csv file is often needed to use that data into a different system. There are two easy ways to do this – using BCP and using SQL Server Management Studio.

Rename a SQL Server database - Sometimes we may need to rename a SQL Server database for testing purposes or when a testing database goes online, we may need to rename it on a production database.

SQL Server General Topics

Create a Business Intelligence Solution in SQL Server 2008 R2 – Free Training - The January 2011 update of the SQL Server 2008 R2 Developers Training Kit contains a FREE Training course designed for developers who want to learn how to build their first Business Intelligence solution with SQL Server 2008 R2.

Find the Most Used Stored Procedures in SQL Server

In one of my earlier posts, I had mentioned about some Dynamic Management Views (DMV) for SQL Server Performance and Tuning. Out of these DMV’s, I find the sys.dm_exec_query_stats very useful. This DMV returns aggregate performance statistics for cached query plans.

One of the suggested methods to get information of the most executed code is to create a trace or use a tool that does that, and then query the results. However since SQL Server caches information over time, you can extract such information using the sys.dm_exec_query_stats

Let us see how to use the sys.dm_exec_query_stats DMV to return the 3 most used stored procedures in your SQL Server database

-- Query by SQLServerCurry.com
SELECT TOP 3 dest.text, deqs.execution_count,
deqs.total_worker_time, dest.objectid
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text (deqs.sql_handle) dest
ORDER BY deqs.execution_count desc

Top Stored Procedures

As you can see, the DMV extracts the 3 most used stored procedure based on its execution count.

Note: If you execute this query on a live databases, the results may be inaccurate in the first run. The BOL says, “The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.” Hence is is advised to run the same query 2 or 3 times.

You can also see some more uses of the sys.dm_exec_query_stats DMV over here Find the Most Time Consuming Code in your SQL Server Database and Find Unused Objects in your SQL Server Database

Most Popular SQL Server Posts in 2010 - SQLServerCurry.com

With 2011 fast approaching and 2010 drawing to an end, I've put together a list of the Most Popular posts on SQLServerCurry.com this year. Along with me, I had SQL Server MVP Madhivanan joining the author panel and sharing his knowledge via posts and answering your comments. Many thanks to you Madhivanan for the useful articles.

A big thank you to everybody who has contributed in one or more of the many ways: by writing a post, by commenting, by sharing the articles on social media, by Subscribing to the RSS feed or by adding me on Twitter.

So here is – a list of 2010's Most Popular Posts on SQLServerCurry.com.

January

Quickly determine the DataType and Nullability of a Column in a query in SQL Server Management Studio 2008

Moving the Tempdb Database to another location

Restore SQL Server 2008 backup in SQL Server 2005

Using SQLCMD to export SQL Server Data as Comma Separated Values

February

When was my SQL Server Installed?

Mapping SQL Server 2000 System Tables to SQL Server 2005/2008 System Views

Identify Tables that have Clustered index but no NonClustered indexes

March

Update a SQL Server Column with Random Numbers within a Range

Temporary Tables VS Table Variables

Profiler for SQL Server 2005/2008 Express Edition

Some useful Addins for SQL Server Management Studio 2005 and 2008

April

Find the Nth Maximum and Minimum Value in a Column

Using CASE Expression in SQL Server to create a Computed Column – Avoiding Nulls

List of Undocumented Stored Procedures in SQL Server

May

SQL Query to find out who attended office on Saturday

Create Date Ranges for Consecutive dates in a SQL Server Table

Taking BackUp of SQL Server Database on a USB Drive

Group and Count Records in SQL Server

June

How to skip a Table or Database while using sp_MSforeachdb or sp_MSforeachtable

Calculate Average Time Interval in SQL Server

Find the Closest Number using SQL Server

July

GROUPBY, HAVING and ORDER BY Usage in SQL Server

Calculate Age from Date Of Birth using SQL Server

NVARCHAR(MAX) VS NTEXT in SQL Server

Find the Most Time Consuming Code in your SQL Server Database

August

Different ways to get Identity of New Inserted Rows in SQL Server

Working with Different Time Zones in SQL Server 2008

When to use STUFF instead of REPLACE – SQL Server

September

Display DML Triggers in your SQL Server Database

Make a T-SQL Query Sleep for a certain amount of time

Email Alerts when something goes wrong with SQL Server

Find Unused Objects in your SQL Server Database

Save SQL Snippets in SQL Server Management Studio (SSMS)

Check if Database Exists In SQL Server – Different ways

Read Environment Variables in T-SQL

October

Check if Temp Table Exists and Drop it in SQL Server

Find and Delete Duplicate Rows From Tables without Primary Key

Calculate Running Total and Row Total in SQL Server

Execute T-SQL Code Only on Certain Days

November

Format Phone Numbers in SQL Server

Count SubTotals and Totals using ROLLUP in SQL Server

Useful SQL Server System Stored Procedures You Should Know

Dynamic Management Views (DMV) for SQL Server Performance and Tuning

Change the Default Language for SQL Server

December

List Dynamic Management Views (DMV) by Category in SQL Server 2008

Select Random Rows from a Table – SQL Server

Does SQL Server support Regular Expressions

Filtered Index in SQL Server 2008

Insert Rows in Temporary Table from Stored Procedure – SQL Server

Which is your best post?

So which was the most useful SQLServerCurry.com post this year. Also let us know the new SQL Server topics or areas you would like us to write in 2011. Your feedback will help us plan better articles for the year 2011.

So here’s wishing you all a very Happy New Year. Have a great time ahead :)

List Dynamic Management Views (DMV) by Category in SQL Server 2008

Dynamic management views and functions have been organized into the different categories which can be viewed over here.

If you want to list the DMV and DMF by category, then here’s a T-SQL query that will help you do so:

SELECT
name as [DMV/DMF Name],
type_desc as [Type],
[DMV Category] =
CASE
WHEN name LIKE 'dm_audit%' THEN 'Auditing Related DMV'
WHEN name LIKE 'dm_cdc%' THEN 'Change Data Capture Related DMV'
WHEN name LIKE 'dm_clr%' THEN 'CLR Related DMV'
WHEN name LIKE 'dm_db%' THEN 'Database&Objects Related DMV and DMF'
WHEN name LIKE 'dm_exec%' THEN 'Execution Related DMV and DMF'
WHEN name LIKE 'dm_xe%' THEN 'Extended Events Related DMV'
WHEN name LIKE 'dm_fts%' THEN 'Full-Text Search Related DMV and DMF'
WHEN name LIKE 'dm_filestream%' THEN 'FileStream Related DMV'
WHEN name LIKE 'dm_io%' THEN 'I/O Related DMV and DMF'
WHEN name LIKE 'dm_sql%' THEN 'Object Ref Related DMV and DMF'
WHEN name LIKE 'dm_provider%' THEN 'Provider Related DMV and DMF'
WHEN name LIKE 'dm_qn%' THEN 'Query Notifications Related DMV'
WHEN name LIKE 'dm_repl%' THEN 'Replication Related DMV'
WHEN name LIKE 'dm_resource%' THEN 'Resource Governor Related DMV'
WHEN name LIKE 'dm_broker%' THEN 'Service Broker Related DMV'
WHEN name LIKE 'dm_os%' THEN 'SQL Server OS Related DMV'
WHEN name LIKE 'dm_server%' THEN 'Server Audit Related DMV and DMF'
WHEN name LIKE 'dm_tran%' THEN 'Transactions Related DMV'
WHEN name LIKE 'dm_cryp%' THEN 'Security Related DMV and DMF'
WHEN name LIKE 'dm_cdc%' THEN 'Change Data Capture Related DMV'
WHEN name LIKE 'dm_database%' THEN 'Transparent Data Encryption Related DMV'
ELSE 'Other DMV'
END
FROM sys.system_objects
WHERE name LIKE 'dm[_]%'
ORDER BY [DMV Category]

As you can see in the query, all we are doing is using a CASE statement and a LIKE operator on the ‘name’ column in the sys.system_objects catalog view and listing the DMV’s and DMF’s in categories.

OUTPUT (Partial)

image

T-SQL and SQL Server Administration Articles Link List – November 2010

Here’s a quick wrap up of the articles published on SQLServerCurry.com in the month of November

T-SQL Articles

Format Phone Numbers in SQL Server - Formatting should be done using your front end application. However if that option does not exist due to some reason, use the following T-SQL code to format Phone Numbers in SQL Server

Count SubTotals and Totals using ROLLUP in SQL Server - The SQL Server ROLLUP operator is useful in generating reports that contain subtotals and totals

Find Column Default Value using T-SQL – SQL Server - Here’s how to find the default value of a Column using T-SQL. We will find the default value of the UnitPrice column in the Products table of the Northwind database

List all Default Values in a SQL Server Database - I had earlier written a query to Find a Column Default value using T-SQL. Here’s how to find the default value of all columns in all tables of a database

Get the Current Stored Procedure Name in SQL Server - I see a lot of developers asking how to get the currently executing Stored Procedure name. This is probably for logging the stored procedures when they are executed.

SQL Server Administration Articles

Search all Stored Procedures where a Table is referenced - A couple of months ago, I wrote a post on how to Search Inside a SQL Server Stored Procedure Definition. A user wrote back asking if it was possible to do the same using an Add-in in SQL Server Management Studio (SSMS).

Filter Bad Words in a SQL Server 2008 Database - If you are using SQL Server 2008, you can use Full-Text Search to filter bad words in a database. The procedure to do so is by creating Stopwords and Stoplists

Drop all Connections to SQL Server Database – Here’s a simple script to drop all active connections to the SQL Server Database. You usually drop connections when you are planning to take the database offline or need to immediately deal with a maintenance issue.

Take a SQL Server Database Offline - This post shows how to take your database offline if there are no current users.

Could not Allocate space for Object because Primary Filegroup Is Full Error - There multiple ways to resolve this error, as suggested above, in the error message itself. Here are some steps that I thought of sharing with my sqlservercurry.com readers

Dynamic Management Views (DMV) for SQL Server Performance and Tuning - In the earlier versions of SQL Server, it was a daunting task to diagnose and resolve performance issues. With DMV’s being introduced in SQL Server 2005 and enhanced in SQL Server 2008, this task has become much easier now.

Change the Default Language for SQL Server - I sometimes get questions like – How do I change the default language of SQL Server 2005/2008 from English to Spanish, so as to display the SQL Server system messages in that language.

Other SQL Server Articles

Next Version of SQL Server ‘Denali’ - If by any chance you missed out on the big announcement made recently during the SQL Pass Summit 2010, then here it is. The SQL Server team announced the next version of SQL Server, code-named ‘Denali’. The current version of SQL Server as of this writing is SQL Server 2008 R2.