This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(313.6, 21%, 40%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Why on production takes to run long time drop table abcde after query below.
On test database drop table abcde after run below query less then 1 sec.
Why on production it takes long time?
CREATE TABLE abcde (
ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY
, Type varchar(10) default 'Grid'
, PageNo int default (0) , RowNo int default (0)
, SRS_ID int default 0 , OracleID int default 0
, EmpDiv varchar(100) default ''
, First_Name varchar(1000) default ''
, Last_Name varchar(1000) default ''
, Preferred_Name varchar(1000) default ''
, Employer varchar(1000) default ''
, empid int default 0
, address varchar(100) default ''
, aa varchar(100) default ''
, bb varchar(100) default ''
, cc varchar(100) default ''
)
INSERT INTO abcde (EmpID, First_Name, Last_Name, aa, bb, cc)
SELECT ID,firstname, lastname, address, state, phone
FROM Employee
WHERE status = 1
SELECT tt.SRS_ID, tt.SRS_ID FROM abcde tt WHERE Type = 'Grid' GROUP BY tt.SRS_ID
SELECT tt.OracleID, tt.OracleID FROM abcde tt WHERE Type = 'Grid' GROUP BY tt.OracleID
SELECT tt.EmpDiv, tt.EmpDiv FROM abcde tt WHERE Type = 'Grid' GROUP BY tt.EmpDiv
SELECT tt.First_Name, tt.First_Name FROM abcde tt WHERE Type = 'Grid' GROUP BY tt.First_Name
SELECT tt.Last_Name, tt.Last_Name FROM abcde tt WHERE Type = 'Grid' GROUP BY tt.Last_Name
SELECT tt.Preferred_Name, tt.Preferred_Name FROM abcde tt WHERE Type = 'Grid' GROUP BY tt.Preferred_Name
SELECT tt.Employer, tt.Employer FROM abcde tt WHERE Type = 'Grid' GROUP BY tt.Employer
SELECT tt.EmpID, tt.EmpID FROM abcde tt WHERE Type = 'Grid' GROUP BY tt.EmpID
SELECT aa, aa FROM abcde tt WHERE Type = 'Grid' GROUP BY aa
SELECT bb, bb FROM abcde tt WHERE Type = 'Grid' GROUP BY bb
SELECT cc, cc FROM abcde tt WHERE Type = 'Grid' GROUP BY cc
How big is the table after the load? Is the table created inside a transaction? Are there any DDL triggers in the database?
What is the output of "SELECT @@version"?
> How big is the table after the load?
INSERT INTO abcde (EmpID, First_Name, Last_Name, aa, bb, cc)
SELECT ID,firstname, lastname, address, state, phone
FROM Employee
WHERE status = 1
(46414 rows affected)
> Are there any DDL triggers in the database?
No any triggers on this database.
> What is the output of "SELECT @@version"?
Microsoft SQL Server 2019 (RTM-CU16-GDR) (KB5014353) - 15.0.4236.7 (X64) May 29 2022 15:55:47 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)
Also, if without GROUP BY in last 11 selects that it fast, but with group by slowly.
Also I see, if I run the query and wait around 10 mins and then drop then it fast, but if I drop immediately after run the query then it slowly.
Interesting. How much RAM is there in the machine? What is "max server memory" set to?
46414 rows is not a lot, but what size in KB does sp_spaceused report?
Hi @Alan5896 ,
Please use below T-SQL to check if any blocking.
USE Master
GO
SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
GO
Did you compare the execution plan between test and production environment.
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
I ran the query -- empty table. Please see attached screenshot.
![USE Master
GO
SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
GO][1]