SQL SERVER – Get Last Running Query Based on SPID

We often need to find the last running query or based on SPID need to know which query was executed. SPID is returns sessions ID of the current user process. The acronym SPID comes from the name of its earlier version, Server Process ID.

To know which sessions are running currently, run the following command:

SELECT @@SPID
GO

In our case, we got SPID 57, which means the session that is running this command has ID of 57.

Now, let us open another session and run the same command. Here we get different IDs for different sessions.

In our case, we got SPID 61. Please note here that this ID may or may not be sequential.

In session with SPID 61, we will run any query. In session with SPID 57, we will see which query was run in session with SPID 61.

Let us run a simple SELECT statement in session with SPID 61 and in session with SPID 57 run the following command.

DBCC INPUTBUFFER(61)
GO

Now, here in DBCC command we have passed the SPID of previous session; we will see the text below. The following image illustrates that we get the latest run query in our input buffer.


There are several ways to find out what is the latest run query from system table sys.sysprocesses.

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 61
SELECT TEXT
FROM
sys.dm_exec_sql_text(@sqltext)
GO

The following image portrays that we get the latest run query in our input buffer.


Please note that in any session there may be multiple running queries, but the buffer only saves the last query and that is what we will be able to retrieve.

There is one more way to achieve the same thing – using function fn_get_sql

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 61
SELECT TEXT
FROM
::fn_get_sql(@sqltext)
GO


All the three methods are same but I always prefer method 2 where I have used sys.sysprocesses.

Today, we have explored a very simple topic. Let me know if you find it useful.

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Reseed Identity of Table – Table Missing Identity Values – Gap in Identity Column

Some time ago I was helping one of my Junior Developers who presented me with an interesting situation. He had a table with Identity Column. Because of some reasons he was compelled to delete few rows from the table. On inserting new rows in the table he noticed that the rows started from the next identity value which created gap in the identity value. His application required all the identities to be in sequence, so this was certainly not a small issue for him.

The solution to this issue regarding gap in identity column is very simple. Let us first take a look at his application’s situation wherein there is missing identity and then we will move on to the solution.

Developers can easily deter the above issue by avoiding gap in sequence of identity column through two additional SQL Tricks of reseeding identity.

We will now see the same example with the solution to the above gap issue. On deleting records, table was reseeded with identity, which was deleted. Download complete SQL Script here.

USE AdventureWorks
GO
/* Create a table with one identity column */
CREATE TABLE TableID (ID INT IDENTITY(1,1), Col VARCHAR(10))
GO
/* Insert 10 records with first value */
INSERT INTO TableID (Col)
VALUES ('First')
GO 10
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Delete last few records */
DELETE
FROM
TableID
WHERE ID IN (8,9,10)
GO
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Get current Max Value and reseed table */
DECLARE @MaxID INT
SELECT
@MaxID = MAX(ID)
FROM TableID
DBCC CHECKIDENT('TableID', RESEED, @MaxID)
GO
/* Insert 10 records with second value */
INSERT INTO TableID (Col)
VALUES ('Second')
GO 5
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Clean Database */
DROP TABLE TableID
GO

I hope is solution is clear to all my readers and they will use it to avoid problems related to gap in identity column. Do send me your feedback on this article and let me know if you all need further explanation.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Shrinking NDF and MDF Files – A Safe Operation

Just a day ago I have received following email from Siddhi and I found it interesting so I am sharing with all of you.

Hello Pinal,

I have seen many blogs from you on SQL server and i have always found them useful and easy to understand. Thanks for all the information you provide.

I have one query about shrinking NDF and MDF files.

Can we shrink NDF and MDF files?? If you do so is there any data loss?

I have been shrinking the .LDF files every now and then but I am not too sure about NDF and MDF files.

Can you please answer my query.

Waiting for your early response.

Regards,
Siddhi

Answer:
Shrinking MDF and NDF file is possible and there is no chance of data loss.

It is not always advisable to shrink those file as those files are usually growing. There are cases when one database is separated in multiple database of any large table is dropped from database MDF and NDF can contain large empty space. This is the time they should be shrank. Shrinking database can be many hours process but it there are very slim chances of data lose.
Following is the script to shrink whole database.
DBCC SHRINKDATABASE (dbName)

Following is the script to shrink single file.
DBCC SHRINKFILE (logicalLogFileName)

To find logicalLogFileName following command has to be ran.
USE dbName
EXEC sp_helpfile

Let us understand this using database AdventureWorks.
/* Shrink Whole AdventureWorks Database */
DBCC SHRINKDATABASE (AdventureWorks)
GO
/* Get the Logical File Name */
USE AdventureWorks
EXEC sp_helpfile
GO
/* Shrink MDF File of AdventureWorks Database */
DBCC SHRINKFILE (AdventureWorks_Data)
GO
Following image of the same process show when whole process is done there will be resultset with information about the new states of the database files.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL – Part 2

Just another day I was playing with my query which I posted earlier SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL and I found that I got error devide by zero. I have fixed this error in following query as well I have updated query to return time in millisecond instead of microsecond. Jerry Hung has also posted similar solution in comments of original article.

I strongly suggest to read original article to now more about introduction and learn about DBCC command which clears cache.

SELECT DISTINCT TOP 10
t.
TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Find Number of Rows and Disk Space Reserved – Using sp_spaceused Interesting Observation

Previously I posted SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL. Today we will look into the same issue but with some additional interesting detail.
We can find the row count using another system SP sp_spaceused. This SP gives additional information regarding disk space reserved on database as well. Well, when I ran the SP on AdventureWorks first time, I suspected that database SP is not providing me correct results.

After a bit investigating I found that it may be possible that due to any reason may be the usage on AdventureWorks database might not be updated. I ran same SP with additional param as shown below and it provided me updated information for the same. Once I ran the SP with @updateusage param, it updated the status of the database. When I ran it again next time without param it gave the same results.

The reason for above behavior is when dellocation of space happened in database (e.g. dropping table or truncating table) it does not release the used space right away. However, when @updateusage param is used database engine scans whole database and update the sapce usage in respective tables.
Let me know if you guys have any interesting observation like this.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL

UPDATE : Updated this query with bug fixed with one more enhancement SERVER – 2008 – 2005 – Find Longest Running Query – TSQL – Part 2.

Recently my company owner asked me to find which query is running longest. It was very interesting that I was not able to find any T-SQL script online which can give me this data directly. Finally, I wrote down very quick script which gives me T-SQL which has ran on server along with average time and maximum time of that T-SQL execution. As I keep on writing I needed to know when exactly logging was started for the same T-SQL so I had added Logging start time in the query as well.

The reason I started to use this script to find out longest running query as if query is changed a bit it will display it as new row and new log start time. This way when I am improving T-SQL query or Stored Procedure I can check their progress in the query and does not have to get bothered with previous data.

I always run following DBCC command before I started to use my query. Following DBCC commands clears the cache of the server and starts fresh logging of the query running time.

DBCC FREEPROCCACHE

Run following query to find longest running query using T-SQL.

SELECT DISTINCT TOP 10
t.
TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC
GO

You can also add WHERE clause to above T-SQL query and filter additionally.

If you have not ran query like this previously on your server I strongly recommend to run this. I bet you will find surprising results.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Check Database Integrity for All Databases of Server

Today we will see quick script which will check integrity of all the database of SQL Server.

EXEC sp_msforeachdb 'DBCC CHECKDB(''?'')'

Above script will return you lots of messages in resultset. If there are any errors in resultset they will be displayed in red text. If everything is black text there is no error. Typical output of above script will be like image included in the article.

Image displayed above is only partial image.

Reference : Pinal Dave (http://www.SQLAuthority.com)

SQL SERVER – Refresh Database Using T-SQL

Yesterday I received following questions on blog. Ashish Agarwal asked following question.

Hi Pinal,

Can we refresh a database (like we do by right clicking database node in object explorer and clicking on refresh) thru SQL Query?
If yes, can you please tell me the query?

Thanks,
Ashish Agarwal

Answer to above question is NO. It is not possible to do the same task using SQL Query.

However, if you have changed some SP or any other object and if they are cached in the database, database can be refreshed using DBCC commands.

Read my previous article about SQL SERVER – Clear SQL Server Memory Caches.

Reference: Pinal Dave (http://www.SQLAuthority.com)

SQL SERVER – 2008 – Interview Questions and Answers Complete List Download

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

Download SQL Server 2008 Interview Questions and Answers Complete List

UPDATE: This article series has been updated with new interview questions and answers series.

Interview is very important event for any person. A good interview leads to good career if candidate is willing to learn. I always enjoy interview questions and answers series. This is my very humble attempt to write SQL Server 2008 interview questions and answers. SQL Server is very large subject and not everything is usually asked in interview. In interview what matters the most is conceptual knowledge and learning attitude.

I have listed all the series in this post so that it can be easily downloaded and used. All the questions are collected and listed in one PDF which is here to download. If you have any question or if you want to add to any of the question please send me mail or write a comment.

SQL SERVER – 2008 – Interview Questions and Answers – Part 1

SQL SERVER – 2008 – Interview Questions and Answers – Part 2

SQL SERVER – 2008 – Interview Questions and Answers – Part 3

SQL SERVER – 2008 – Interview Questions and Answers – Part 4

SQL SERVER – 2008 – Interview Questions and Answers – Part 5

SQL SERVER – 2008 – Interview Questions and Answers – Part 6

SQL SERVER – 2008 – Interview Questions and Answers – Part 7

SQL SERVER – 2008 – Interview Questions and Answers – Part 8

Download SQL Server 2008 Interview Questions and Answers Complete List

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – Interview Questions and Answers – Part 8

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

SQL SERVER – 2008 – Interview Questions and Answers Complete List Download

What is Data Compression?

In SQL SERVE 2008 Data Compression comes in two flavors:

  • Row Compression
  • Page Compression

Row Compression

Row compression changes the format of physical storage of data. It minimize the metadata (column information, length, offsets etc) associated with each record. Numeric data types and fixed length strings are stored in variable-length storage format, just like Varchar.  (Read More Here)

Page Compression

Page compression allows common data to be shared between rows for a given page. Its uses the following techniques to compress data:

  • Row compression.
  • Prefix Compression. For every column in a page duplicate prefixes are identified. These prefixes are saved in compression information headers (CI) which resides after page header. A reference number is assigned to these prefixes and that reference number is replaced where ever those prefixes are being used.

Dictionary Compression.

Dictionary compression searches for duplicate values throughout the page and stores them in CI. The main difference between prefix and dictionary compression is that prefix is only restricted to one column while dictionary is applicable to the complete page.

What is use of DBCC Commands?

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.  DBCC commands are used to perform following tasks.

  • Maintenance tasks on database, index, or filegroup.
  • Tasks that gather and display various types of information.
  • Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.
  • Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.

(Read More Here)

How to find tables without Indexes?

Run following query in Query Editor.

USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
,name AS table_name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'IsIndexed') = 0
ORDER BY schema_name, table_name;
GO

How to copy the tables, schema and views from one SQL Server to another?

There are multiple ways to do this.

  1. “Detach Database” from one server and “Attach Database” to another server.
  2. Manually script all the objects using SSMS and run the script on new server.
  3. Use Wizard of SSMS. (Read More Here)

How to copy data from one table to another table?

There are multiple ways to do this.

1) INSERT INTO SELECT

This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are not required to list them.

2) SELECT INTO

This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.

(Read More Here)

What is Catalog Views?

Catalog views return information that is used by the SQL Server Database Engine. Catalog Views 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. All user-available catalog metadata is exposed through catalog views.

What is PIVOT and UNPIVOT?

A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table.

UNPIVOT table is reverse of PIVOT Table. (Read More Here)

What is Filestream?

Filestream allows you to store large objects in the file system and have these files integrated within the database. It enables SQL Server based applications to store unstructured data such as documents, images, audios, videos etc. in the file system. FILESTREAM basically integrates the SQL Server Database Engine with New Technology File System (NTFS); it basically stores the data in varbinary (max) data type. Using this data type, the unstructured data is stored in the NTFS file system and the SQL Server Database Engine manages the link between the Filestream column and the actual file located in the NTFS. Using Transact SQL statements users can insert, update, delete and select the data stored in FILESTREAM enabled tables.

What is Dirty Read ?

A dirty read occurs when two operations say, read and write occurs together giving the incorrect or unedited data. Suppose, A has changed a row, but has not committed the changes. B reads the uncommitted data but his view of the data may be wrong so that is Dirty Read.

What is SQLCMD?

sqlcmd is enhanced version of the isql and osql and it provides way more functionality than other two options. In other words sqlcmd is better replacement of isql (which will be deprecated eventually) and osql (not included in SQL Server 2005 RTM). sqlcmd can work two modes – i) BATCH and ii) interactive modes. (Read More)

What is Aggregate Functions?

Aggregate functions perform a calculation on a set of values and return a single value. Aggregate functions ignore NULL values except COUNT function. HAVING clause is used, along with GROUP BY, for filtering query using aggregate values.

Following functions are aggregate functions.

AVG, MIN, CHECKSUM_AGG, SUM, COUNT, STDEV, COUNT_BIG, STDEVP, GROUPING, VAR, MAX, VARP (Read More Here )

What do you mean by Table Sample?
TABLESAMPLE allows you to extract a sampling of rows from a table in the FROM clause. The rows retrieved are random and they are not in any order. This sampling can be based on a percentage of number of rows. You can use TABLESAMPLE when only a sampling of rows is necessary for the application instead of a full result set. (Read More Here)

What is Row_Number()?
ROW_NUMBER() returns a column as an expression that contains the row’s number within the result set. This is only a number used in the context of the result set, if the result changes, the ROW_NUMBER() will change.

What are Ranking Functions?

Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic. Different Ranking functions are:

ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set.

DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking. (Read More Here )

What is the difference between UNION and UNION ALL?

UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table. (Read More Here)

What is B-Tree?

The database server uses a B-tree structure to organize index information. B-Tree generally has following types of index pages or nodes:

  • root node: A root node contains node pointers to branch nodes which can be only one.
  • branch nodes: A branch node contains pointers to leaf nodes or other branch nodes which can be two or more.
  • leaf nodes: A leaf node contains index items and horizontal pointers to other leaf nodes which can be many.

© Copyright 2000-2009 Pinal Dave. All Rights Reserved. SQLAuthority.com

Reference : Pinal Dave (http://blog.SQLAuthority.com)