Find Unused Objects in your SQL Server Database

1. One option is to run the profiler, capture the results and analyze if any objects are used at all. This should be done for a certain period of time. If any application is connected to the database, run each and every functionality available in front end and, capture and analyze the profiler result.

2. Another method is to rename certain objects that you think are not being used and observe the logs over a period of time and check for any code breaks. This is based on a trial-and-error method.

3. Another alternative is via a query

SELECT source_code,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 last_execution_time desc

This query wont give you a list of unused objects. It gives you a list of used objects which you need to keep track for some time and see if any of the objects are not used. It is like monitoring trace result for sometime to determine if any objects are not used.

Find the Most Used Stored Procedures in SQL Server

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 thesys.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

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.

XML Basics in SQL Server 2008

The first step is to start ‘SQL Server Management Studio’ and start a new query window. Then use the following clauses

1. FOR XML AUTO – This clause returns a simple, nested XML tree result.

SELECT * FROM Customers FOR XML AUTO

2. FOR XML RAW – This clause returns a simple, nested XML tree result by transforming each row in an <ROW/> Element.

SELECT * FROM Customers FOR XML RAW

3. FOR XML AUTO, ELEMENTS – This clause returns a XML result by specifying columns as sub elements.

SELECT * FROM Customers FOR XML AUTO,ELEMENTS

4. FOR XML AUTO, ELEMENTS, TYPE – This clause returns a XML result by specifying columns as sub elements and ‘TYPE’ specifies that it returns result as XML type which we can store in XML data type in SQL Server.

SELECT * FROM Customers FOR XML AUTO, ELEMENTS, TYPE

In SQL Server 2005, Microsoft has introduced a new data type ‘XML’. We can use this data type to store well formed as well as valid xml in our tables. Let’s see a few examples of the same –

Let’s first create a table which will use XML data type as shown in the following script –

Now let’s insert few rows in our table. For inserting the data in the above table, we will define a variable with ‘XML Data Type’ as shown below – 



Now let’s write a select statement which will fetch the inserted data – 

SELECT * FROM CustomerProducts

Now if you check the result, it should look like the following – 

Now let’s write some queries which will test the XML data type. Let’s insert a record into our ‘CustomerProducts’ table which is not well formed, as shown below –

If you check the above XML, it does not having closing tag for <ProductID>. So the result will be as shown below – 



Now let’s see how to validate the XML data using XML Schemas. Let’s drop the existing table we created above. 

DROP TABLE CustomerProducts

Now create a schema for XML data validation as shown below – 

CREATE XML SCHEMA COLLECTION ProductSchema AS'
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
targetNamespace="http://www.microsoft.com/schemas/northwind/products" 
xmlns:prod="http://www.microsoft.com/schemas/northwind/products">
<xs:element name="Product">
<xs:complexType>
<xs:sequence>
<xs:element ref="prod:ProductID" />
<xs:element ref="prod:ProductName" />
<xs:element ref="prod:SupplierID" />
<xs:element ref="prod:CategoryID" />
<xs:element ref="prod:QuantityPerUnit" />
<xs:element ref="prod:UnitPrice" />
<xs:element ref="prod:UnitsInStock" />
<xs:element ref="prod:UnitsOnOrder" />
<xs:element ref="prod:ReorderLevel" />
<xs:element ref="prod:Discontinued" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="ProductID" type="xs:integer" />
<xs:element name="ProductName" type="xs:string" />
<xs:element name="SupplierID" type="xs:integer" />
<xs:element name="CategoryID" type="xs:integer" />
<xs:element name="QuantityPerUnit" type="xs:string" />
<xs:element name="UnitPrice" type="xs:double" />
<xs:element name="UnitsInStock" type="xs:integer" />
<xs:element name="UnitsOnOrder" type="xs:integer" />
<xs:element name="ReorderLevel" type="xs:integer" />
<xs:element name="Discontinued" type="xs:boolean" />
</xs:schema>'

To see all the available XML schemas execute below query – 

SELECT * FROM sys.xml_schema_collections

The result is as shown below – 



Now let’s create the CustomerProduct table once again with the XML data type which will take the address of the above schema, as shown below – 



Now try to insert a record and you will get an exception – 



The exception is thrown because we are not passing the ProductName. Now let’s insert the following record which matches our schema validations – 

You can now query the data and you should see valid XML.

Export Table to CSV

Note: To handle complex scenarios and large files, use a utility like DTS/SSIS (SQL Server Integration Services). However for simpler scenarios, the approach shown in this article works fine.

Create a test table

CREATE TABLE test(
empid varchar(6),
empname varchar(100),
dob datetime,
salary decimal(12,2)
)

Consider the following data

INSERT INTO test
SELECT 'EMP001','Suresh','19910619',3000
UNION ALL
SELECT 'EMP002','Ramesh','19710103',20000
UNION ALL
SELECT 'EMP003','Nilesh','19800722',4760
UNION ALL
SELECT 'EMP004','Kumar','19680911',42000

Method 1: Use bcp in command prompt

Usually when we use bcp to export data, by default column values are separated by a tab.
So we need to use a format file to make the column delimiter a comma instead of a tab.

Create a file named format.fmt in F drive (or whichever drive is available in your system)
with the following data. You can also download format.fmt over here

Over here, 9.0 refers to the SQL Server Edition (in this case SQL Server 2005) and 4 represents the number of rows (in this case 4 rows).

Now open the command prompt and use the following bcp command in it

bcp yourdb..test out D:\test.csv -T -f D:\format.fmt

The file named F:\test.csv will be created with values separated by comma.

Method 2: Use Management studio

Right click on the database and select Tasks > Export data

Select Data source as SQL Server, select the server name, authentication and database and click Next

Select Destination as Flat file Destination and browse for a .csv file and click Next

Select row terminator as {CR}{LF} and column terminator as comma{,} and click Next

At the end it will show the details of the rows which got exported to .csv file.

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

Method 1 : Use Dynamic Management View

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.

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

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.

Find the Most Time Consuming Code in your SQL Server Database

 Note that a time consuming code may not necessarily be inefficient; it also depends on the volume of data being processed.

--Top 10 codes that takes maximum time
select top 10 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

--Top 10 codes that takes maximum physical_reads
select top 10 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_physical_reads desc

 

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.

The first query sorts data based on descending order of total_elapsed_time and second query by total_physical_reads.

Find Last Run Query in SQL 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

Load Comma Delimited file (csv) in SQL Server

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.

Consider the following data

1,test,89300
2,testing,52801
3,test,1000

Create a file name test.csv in your system and add the data shown above in that file

Create a test table

CREATE TABLE test(
id int,
names varchar(100),
amount decimal(12,2)
)

Method 1: Using Bulk Insert

Here’s the same query for you to try out

bulk insert test from 'F:\test.csv'
with
(
fieldterminator=',',
rowterminator='\n'
)

The above code reads data from the file located at F:\text.csv and splits data into different
columns based on the fieldterminator ‘,’ (comma) and into different rows based on therowterminator '\n' (\n is for newline).

Now if you do a SELECT * FROM test you will get the following output

Method 2: Using SQL Server Management Studio

Right click on your database > Tasks > Import data

Select the datasource as Flat file. Select the file using the browse button or type the file path and name directly and click Next

Select row terminator as {CR}{LF} and column terminator as comma{,} and click Next

Select Destination as your server and select the database where the table exists. Click Next

The wizard will import the data and show you the details about the data which was imported