Unable To KILL SPID Using WITH STATUSONLY In SQL Server

KILL is one of the common commands that we from the database side work on a regular basis. It would be obvious thinking for some of the SQL users to come up with a statement as “KILL using WITH STATUSONLY is not killing the session”.

The WITH STATUSONLY by the name is going to show us the status of the session ID passed along with the KILL command. Additionally, the status will be shown for the corresponding session ID only when your session is in the rollback status.

For example, just imagine that you killed an ALTER

Let’s check a simple example of killing a session WITH STATUSONLY

I have created a table and imported bulk records into a table as “Kill_test”. After inserting the records into the “Kill_test” table, I will be selecting the data as:

SELECT * FROM Kill_test

While the data is being selected, simultaneously I will be running the KILL WITH STATUSONLY command as below. In my case, the session ID is 110.

KILL

In this case, we may notice error as mentioned below.

This happens because the KILL (spid) WITH STATUSONLY in our case was a SELECT command. 

The WITH STATUSONLY gathers and shows status of the session only when the session is in the rollback state.

Fix and Bottomline:

If you feel that your session is not being killed using the WITH STATUSONLY command, then it’s a misconception of killing the session ID. Please use the KILL (spid) alone to finish the job.

KILL (spid)

Example:

You may also refer: "How to kill a SPID in SQL Server"

For your testing purposes

Please try to create a clustered index on a heap table (which doesn’t consist of a clustered index) consisting of a huge number of records. Before performing this action, please make sure to run the following in the development environment. After a minute of execution time, kill the session. The killed session would be in the KILLED/ROLLBACK state. Now, once again run the KILL (spid) WITH STATUSONLY to check the current status of the killed session.

The Business Intelligence Semantic Model In SQL Server

The Business Intelligence Semantic Model is the latest model created by Microsoft that supports all the applications in BI stacks. Additionally, the intent of such a model is to provide efficient, rich, and scalable analytical capabilities. The important highlight of the BISM model is its integration of data from heterogeneous data sources like LOB applications, relational databases, and unconventional sources like cloud, Excel, or text files. Further, the BISM comes with a three-layer architecture: the data model, business logic and queries, and data access

Data Model:

Now, it’s the choice of the developers or analysts working in BISM architecture to choose either a multidimensional data model or a tabular one. Additionally, the integration of the model can also be in an Analytical Services server or PowerPivot.

Business Logic and Queries:

Yet another choice for the analysts and developers to choose between the Multidimensional Expressions (MDX) and Data Analysis Expressions (DAX).

MDX – The MDX is based on the multidimensional concepts that are like query language for online analytical processing (OLAP). Further, the OLAP uses a database management system. And the language can also be used for calculation purposes like spreadsheet formulas.

DAX – The DAX is based on the tabular concepts which are a native query language for Microsoft PowerPivot, Power BI and SQL Server Analytical Services (SSAS). Some of the similar formulas used in the Excel is as well used in the DAX for formulating purposes. The purpose of DAX is to facilitate with simple, reliable and flexible feature while encroaching the PowerPivot and SSAS tabular models.

Data Access Layer:

The data access layer merges the data from multiple data sources such as relational databases like the DW, files line of business (LOB) applications. The data can be either server locally or can be passthrough directly from the data sources. Further, serving locally is called the “Cached mode” and passthrough is called the “pass-through mode”.

Cached Mode: The cached mode can use either of the two storage engines. One that comes with the MOLAP that supports the multidimensional model and the other latest engine is “VertiPaq”.

Pass-through Mode: By the name just accept the data without saving the data from the data source. However, the ROLAP and DirectQuery is the complementing model to the cached mode counterpart. 

Interview questions you may be interested:

"Difference Between Clustered Index And Non-Clustered Index In SQL Server"

"Difference Between Shared Lock, Exclusive Lock And Update Lock In SQL Sever"

Using Dynamic Pivot Tables In SQL Server

PIVOT relational operator is one of the best features that the SQL Server consists of. PIVOT revolves around a table consisting of unique values into multiple columns. A lot of reporting companies, Data Warehouse and analytical consultancies use the PIVOT for their industrial purposes. A simple pivot just rotates the table value to multiple columns. However, if we need the values for pivot column to be automated, then dynamic PIVOT is the best option for us.

We are going to learn about using the dynamic pivot in our SQL tables.

In my example, I am going to create a table and insert the values about the list of table fragmentations. Let’s check the steps on performing a dynamic PIVOT.

Step 1: Inserted 10 random of my database fragmentation records into #1 temp table

select * from #1

Step 2: Implementing a dynamic PIVOT on #1 temp table

We are going to pivot on [date] field column, taking into account the max function on (fragmentation) column.

DECLARE @Pivot_Column [nvarchar](max); 
DECLARE @Query [nvarchar](max); 
SELECT @Pivot_Column= COALESCE(@Pivot_Column+',','')+ QUOTENAME(date) FROM 
(SELECT DISTINCT [date] FROM #1 )Tab
SELECT @Query='SELECT TableName,DatabaseName, '+@Pivot_Column+'FROM  
(SELECT TableName, [date] , DatabaseName,Fragmentation  FROM #1   )Tab1 
PIVOT 
( 
MAX(Fragmentation) FOR [date] IN ('+@Pivot_Column+')) AS Tab2 
ORDER BY Tab2.TableName' 
EXEC   (@Query)

Ordering Dynamic PIVOT column

Currently, I have just one data in my temptable for all 10 records. But, imagine if you need to dynamically PIVOT for multiple date field columns and they should be in an ordered manner. Then, the order by clause should be added to the PIVOT parameter as shown in the code below.

DECLARE @Pivot_Column [nvarchar](max); 
DECLARE @Query [nvarchar](max); 
SELECT @Pivot_Column= COALESCE(@Pivot_Column+',','')+ QUOTENAME(date) FROM 
(SELECT DISTINCT [date] FROM #1 )Tab Order By date
SELECT @Query='SELECT TableName,DatabaseName, '+@Pivot_Column+'FROM  
(SELECT TableName, [date] , DatabaseName,Fragmentation  FROM #1   )Tab1 
PIVOT 
( 
MAX(Fragmentation) FOR [date] IN ('+@Pivot_Column+')) AS Tab2 
ORDER BY Tab2.TableName' 
EXEC   (@Query)

Find Last Update Statistics Date In SQL Server

I was working on a query to improve the performance and I happened to find the Update Statistics on the table outdated. Update Statistics play an important role in query performance. If you are a DBA or a database developer, the update statistics is one common term you keep listening to every day. Additionally, one fantastic feature we have in SQL Server is the jobs. We can always have the Update Statistics updated by scheduling jobs.

Today, we will be checking the last updated statistics date using the system view - sys.indexes. Before checking the last updated statistics date, let’s check the indexes we have on the desired table.

I have created a dummy table as “Hospital”. I will be checking the indexes on that table with the below command.

sp_helpindex Hospital

Once you have the list of indexes from the sp_helpindex command for your information, let’s check the last updates statistics date with the below query.

SELECT name AS Index_Name,
STATS_DATE(OBJECT_ID, index_id) AS Last_UpdateStats
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('your table name')

NOTE: If you have created a new table and notice NULL values in the date field, then the SQL Server never created a statistic blob on your table. The statistic blobs are not created for new tables, empty tables, or filtered statistics. Due to these reasons, the results can go NULL for you and the predicate does not return any rows as results. 

If any of your statistics do not match the indexes of your table, then they appear in the sys.stats instead sys.indexes.

SELECT Stats_ID, [name] AS Stats_Name,
STATS_DATE(object_id, stats_id) AS [Stats_Date] 
FROM sys.stats s 
WHERE s.object_id = OBJECT_ID('your table name');