March 8, 2017 at 5:07 am
My database monitor tool, SolarWinds DPA, shows this INSERT coming from SSMS on my machine:
Although the INSERT fails because of the primary key, [Mocs.Portal].Statement is a huge table of 120+GB with about 11 millions records and it takes good few minutes before it fails.
I have no idea where this INSERT comes from. No one here ran it explicitly and it happened already 3 times. Any ideas what could it be?
March 8, 2017 at 5:18 am
Looks like the command behind an index rebuild (but that wouldn't fail due to a key violation)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2017 at 5:33 am
Thanks, maybe it indeed didn't fail.
Can you please elaborate in short what could have happened? There was indeed a CREATE INDEX statement for the table but how could it result in the monitor tool catching the INSERT?
Can I reproduce the situation on a small table?
March 8, 2017 at 5:45 am
Eliyahu - Wednesday, March 8, 2017 5:33 AMThanks, maybe it indeed didn't fail.
Can you please elaborate in short what could have happened? There was indeed a CREATE INDEX statement for the table but how could it result in the monitor tool catching the INSERT?
When you create/rebuild an index, there's a background command of the form INSERT INTO <table name> SELECT * FROM <table name> run to populate the new index. Your monitoring tool caught that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2017 at 5:57 am
Thanks! I had a similar issue last week and thought it was sloppy programming. The query blocked 7 other jobs. Good to keep in mind for next time.
March 8, 2017 at 6:18 am
Thanks.
The mechanism is still not clear to me. I add index with an CREATE INDEX TSQL, not using any design tools. Is SSMS that smart to add an INSERT to the CREATE INDEX? Had it been done on the server side as a part of index creating process, I would've understood it better. Or, perhaps, the INSERT is added on the server side but the monitor toll confuses it for a client SQL?
March 8, 2017 at 6:45 am
In my case it's a stored procedure that's apparently doing it but there are hundreds of them and none are documented so the task was daunting. However, with what the monitoring system provided and knowing the purpose, I can refine the review. I know - I've done re-indexing without this query but if I find anything, I'll post back.
March 8, 2017 at 7:00 am
Eliyahu - Wednesday, March 8, 2017 6:18 AMIs SSMS that smart to add an INSERT to the CREATE INDEX?
No. SSMS is just sending commands to SQL Server, it doesn't think about what the commands mean.
Had it been done on the server side as a part of index creating process, I would've understood it better.
But that's exactly what is happening. That's what I said, background commands being run by SQL Server to actually process the create index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2017 at 7:21 am
PGarberick - Wednesday, March 8, 2017 6:45 AMIn my case it's a stored procedure that's apparently doing it but there are hundreds of them and none are documented so the task was daunting. However, with what the monitoring system provided and knowing the purpose, I can refine the review. I know - I've done re-indexing without this query but if I find anything, I'll post back.
You can query on sys.sql_modules definition column to find words in stored procedures of a database. So you can do things along the lines of: SELECT *
FROM sys.sql_modules
WHERE [definition] like'%GO PACK GO%'
Or whatever you want. Might want to search on %ALTER INDEX% or %CREATE INDEX%
Nice avatar by the way!
Sue
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply