ahughes257
commented
2 years ago have you come across an invisible index ?
SQL Server 2012 SP4
if not exists ( select 1 from sys.schemas s inner join sys.tables t on s.schema_id=t.schema_id inner join sys.indexes i on t.object_id=i.object_id and i.name = 'indexname' where t.name = 'table') CREATE UNIQUE NONCLUSTERED INDEX [indexname] ON [dbo].table(col1 ASC, col2 ASC, col3 ASC, col4 ASC) WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF, FILLFACTOR =80) ON [PRIMARY];
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'schema.table' and the index name 'indexname'. The duplicate key value is (
i simply cant get to the bottom of this one - its not in the sys schema or displayed on the table but i cant recreate it as it exists ?
LitKnd
Comment history migrated from wordpressRodrigo January 28, 2016 9:14 am Kendra, great post (again).
I have a question, It’s a bit off topic (if you think that this discussion doesn’t belong here, feel free to remove my comment, no heart feelings).
From time 2 time I need to deal with indexes created by Tuning Advisor “experts”, that don’t bother to look if there indexes already covering those columns.
Here is the question: any chance of a post about overlapping indexes (different indexes covering the same columns) and how they can affect performance?
Loading... Reply Kendra Little January 28, 2016 9:20 am That’s an awesome question. Instant gratification: I did a webcast on this topic that you can watch for free on the Idera site! https://www.idera.com/resourcecentral/webcasts/sqlserver/geeksync/consolidating-indexes-in-sql-server
The webcast goes from simple duplicate examples up through more advanced overlapping scenarios and also talks about some tricky situations like a duplicate NC to the clustering key.
Loading... Reply Rodrigo January 28, 2016 9:58 am Thanks for replying, I I’ll watch the video.
=D
Loading... Reply Leonard A Barnhill January 28, 2016 11:36 am Hey Kendra, I am one of those Accidental DBAs so this may be common knowledge but, how would you determine the existence of an index if you do not know the name of the index? I would want to know whether an index exists on a table and the name of it.
Leonard
Loading... Reply Kendra Little January 31, 2016 1:36 pm Great question!
For a super quick and simple way to query the index names on a table in TSQL, this query will do it:
SELECT si.index_id, si.name as index_name FROM sys.indexes AS si JOIN sys.objects AS so on si.object_id=so.object_id JOIN sys.schemas AS sc on so.schema_id=sc.schema_id WHERE sc.name=’agg’ / Schema / AND so.name =’FirstNameByYear’ / Table /; GO
I’ll publish a post later this week in a few weeks with more detail on ways to script out the index definitions and sizes, including gotchas about filters, compressions, and partition settings that might be sneaking around in there.
Loading... Reply Finding Indexes – Curated SQL January 29, 2016 5:00 am […] Kendra Little talks about index discovery: […]
Loading... Reply Alin April 20, 2016 1:08 am Hi Kendra
Nice post… Another drawback for DROP_EXISTING is that if you cancel the query that creates the index, the existing one (assuming one existed), is dropped.
Loading... Reply Kendra Little April 20, 2016 11:19 am Oh, very interesting!
Loading... Reply Binil September 28, 2016 3:01 am IF INDEXPROPERTY(OBJECT_ID(‘[dbo].[T_RECON_DIFF]’), ‘NCI_T_RECON_DIFF_ReconDate’, ‘IndexID’) IS NULL BEGIN CREATE NONCLUSTERED INDEX [NCI_T_RECON_DIFF_ReconDate] ON dbo.T_RECON_DIFF(ReconDate); END GO
Loading... Reply Tim October 26, 2016 11:01 am Like the website and like the Dinosaur Roar storybook reference!!
Loading... Reply Stephen Anslow October 24, 2017 11:52 am Bookmarked this page a long time ago, but now have a Blocking Situation that I wonder if OBJECT_ID() is responsible for…
This is on SQL 2016 Standard SP1 CU2, in case that matters…
We run a 5-wide reader-process from our App Server (each session being autonomous, firing at-will from the App to the DAL to SQL with a payload request).
We’re noticing a bunch of blocking in an SP that doesn’t have SELECT…INTO #Temp… or ALTER TABLE #Temp, but it has an object-existence IF statement that always runs:
IF OBJECT_ID(‘tempdb..#FullChainInventory’) IS NULL
Given the target table is the same name across all 5 threads, could SQL’s temp object caching be getting in the way due to the Shared Schema Lock you spoke of? Given a bit of latitude, I can try to persuade the powers that be to allow me to replace them with the schema-objects or even a schema-tables JOIN, but there’s always red-tape…
Your thoughts?
Thanks!
Loading... Reply Kendra Little October 27, 2017 10:36 am Hi Stephen,
Why check for the object existence at all? My guess is that the temp table may also be dropped at the end of the procedure — this just results in a renaming dance in tempdb behind the scenes. More info in the “CREATE and DROP, Don’t” section here: http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx
How long is the duration of the blocking when it happens?
Loading... Reply Stephen Anslow October 27, 2017 11:11 am Thanks for taking the time to respond to an old thread, Kendra.
These are two temp tables created by the calling Proc, so instead of a SQL nasty red message or a TRY-CATCH block, the code checks for the existence of each one, generating its own nasty-red message via RAISERROR with State 17 – typical of this Solution for the last 15 years.
Blocking this time was 4 seconds. Although not long in some folks’ eyes, the wait type was LCK_M_IX and the lead blocker and blocked were all the same procedure. The only schema-level operations in the code are 2 OBJECT_ID() checks for existence. Schema-level blocking concerns me especially, because the process is one of the most time-critical, run hundreds, if not thousands, of times per Daily Run, so blocking-elimination is really high on the hit-list.
I’ve hunted for other potential blocking constructs in the code, but we only use temp tables as targets of INSERT/UPDATE and persisted tables are read-only. There are no Global temp tables either.
The database uses read_committed_snapshot at the DB level to obviate the reader/writer-blocking issue pre SQL 2005 (acknowledged there are a few nuances to RCS but we haven’t hit them as far as we know…)
Hence the grasping at straws type question re temp object caching…
Loading... Reply Kendra Little October 27, 2017 12:30 pm No worries. Thanks for the interesting case!
The long blocking duration is a little puzzling to me. Setting up a very simple attempt at repro, I can generate some blocking if I make the procedure execute very very quickly — like 1,200 executions per sec from each session. But it’s just occasional 1ms or 2ms LCK_M_X and LCK_MS waits– and the predominant waits are PAGELATCH types.
As soon as I add in some data access and the execution goes to around 30/sec on each session, then these lock waits don’t appear.
So there’s something I’m missing in the repro.
How long does your calling proc take to run on average? Is it inserting into the temp table in both the outer/calling proc and the inner proc? Are there updates or deletes against the temp tables? Are there indexes created on the temp tables at any point (and if so, when)?
Loading... Reply Stephen Anslow October 27, 2017 1:31 pm One of the tables is pre-loaded in the caller with sometimes 1, or up to 4 of the 6 columns populated. Usually no Indexes on that table… The called proc does a bit of sanity DELETE based on a 2-column match, then goes and tries to set 2 “key” columns (SiteID and ItemMasterID) because SOME callers haven’t set them (though they could).There are additional INSERTs and UPDATEs, but no indexes are created WITHIN the called proc. Just one of the calling procs defines the temp table’s InventoryID as a PK as part of the CREATE TABLE itself. It happens that Solarwinds informs of the calling Proc for the blocked SPIDs, but it doesn’t give me the caller for the lead blocker! (rats!). As I know the workload, it is highly possible that the “Initialize” proc, that creates WITH the PK, is the lead blocker.
The 2nd one is also a HEAP that sustains CUD actions. None of the callers, or the called, creates any index, before, during or after the called proc EXEC.
Duration is somewhat variable! It’s dependent upon how the calling App (a C# Replenishment “Engine”) is configured. The item count can vary from 500 to 5000 or more, and the supply chain rules are also configurable… So how long is my piece of string? Depends on configurations and the sheer power of the SQL Server! I wish I could answer the “how long” question! There’s no logging to tell me, so I’d have to find a “gimme average duration for this proc” T-SQL query. I will readily admit that due to the 20 Production servers we look at via Solarwinds, we are “exception” driven, and tool-reliant. If the blocked proc doesn’t figure as a top-40 entrant, we don’t see its metrics in Solarwinds, and rarely have time to go back to basics with targeted T-SQL. (Confessions of a 1-person “performance monitor” here endeth!) As suspected, unless I can get to the actual blocked STATEMENT in the Proc, Solarwinds gives me precious little to work with.
Thanks again for your willingness to delve… I think we can only surmise what may have occurred as the precise resource the LCK_M_IX is waiting for isn’t available.
Until next time…
Loading...
Kendra Little October 30, 2017 10:37 am Do you have the overall wait stats from the time period it occurred, and just before and after?
Like you’re saying, it would be great to figure out the wait stats for the block leader. It sounds like you don’t have the granular data for the block leader, but if you have the overall server level wait stats and there’s a wait in there like THREADPOOL or RESOURCE SEMAPHORE, then that information could also help lead to an educated guess about what might be stalling the lead of the blocking chain.
Loading... Stephen Anslow October 30, 2017 5:42 pm Zero THREADPOOL or RESOURCE_SEMAPHORE for the timeslice, which in Solarwinds DPA is just the 10 minutes, on a minutely slice, at the time the blocking occurred.
That “exact resource” that blocking is caused by is so elusive… Neither SQL Monitor nor Solarwinds DPA do a decent job of revealing that information.
Aside from memory/CPU (which is good, at 1h16m – 4600 seconds), all I have are:
FT_IFTSHC_MUTEX, HADR_FILESTREAM_IOMGR_IOCOMPLETI (Odd, because there’s ZERO HA configured!), SQLTRACE_INCREMENTAL_FLUSH_SLEEP, WAIT_XTP_HOST_WAIT – each of these 4 incurred 600~ seconds for the 10 minute slice.
Other wait types then drop to: 350 seconds for CXPACKET 300 for BROKER_TO_FLUSH 250 for EC (Seems that Microsoft is using this for the Buffer Pool Extension, which we have configured) 200 for PREEMPTIVE_OS_GETPROCADDRESS and then precipitously PAGEIOLATCH_SH at a whopping 32 seconds, _EX at 26 seconds PAGELATCH_UP 11 WRITELOG 10 and our target LCK_M_X (there are a mixture of _X and _IX but _X gets the 5 seconds nod).
Not much of anything to go on, it seems.
Chalking it up to “experience”. May go with a TRY-CATCH around the first operation on the temp input table, just for an experiment…
Cheers!
Loading... Dylan Nicholson July 3, 2019 5:37 pm DROP INDEX IF EXISTS agg.FirstNameByYear.ix_halp sounds expensive though – if the index is already there and potentially would take a long time to rebuild, why would you drop and recreate?
Loading... Reply Kendra Little July 4, 2019 1:44 pm Right— that’s why I wrote that it’s just for index cleanup. It does not help with the problem.
Loading... Reply