Tag Info

New answers tagged

1

In order for the table to be partitioned, your clustered index should be created as:- ALTER TABLE [dbo].[Logs] ADD CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED ( [logId] ASC, [time] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ...


3

Your first column in the index is cl_totalcrawltimes. In the WHERE clause you're using this column to define a lower range (cl_totalcrawltimes > 0). Depending on the data distribution this most probably will lead to an index scan instead of an index seek. Furthermore, cl_crawlsource is not part of the index (the include section, preferrably), so this would ...


4

Some context The first things to observe is that your clustered index does not help with the lookup of the tid column, because t is the leading column in the index. If you flipped the order of t and tid in the key, I would expect the index hint to go away and the query to run faster without adding any new indexes. Specific Answer The most likely reason ...


1

The index should work as is. As pointed out by @jjanes, you just needed to run ANALYZE. However, I suggest you modify the index. The expression in your definition is not useful. CREATE INDEX foo_idx ON intentions ((data LIKE '%foo%')) WHERE data NOT LIKE '%foo%'; The expression is always FALSE and useless noise. Either simplify to the slightly cheaper ...


1

I needed to run ANALYZE for the proper query plan to take effect. Once this was done, everything worked like a charm. My final index looks like this: CREATE INDEX foo_idx ON intentions ((data LIKE '%foo%')) WHERE data NOT LIKE '%foo%';


1

The NOT LIKE will be problematic. Imagine this query without the NOT. The index could be walked to find the interval of values (indexes are sorted lists) which match. The NOT means you want everything else, both before and after the matching interval. It's a pretty special optimiser which will do that. You may be able to invert the indexes to identify ...


1

You could try a slightly different approach. CREATE INDEX foo_idx ON mytable (strpos(name,'foo')); SELECT * from mytable where strpos(name,'foo') = 0; Index on Expression


1

With only 5 values the index isn't honing to be very selective for joining so it'll pretty much always index scan unless looking for just one value. That query plan/anal output suggests that it thinks half the work is in matching the rows there with the CTE, could you try run the group+aggregate over the whole lot. If it is index scanning anyway you might ...


3

Going out on a limb here (basic information is missing), partial indexes will probably be your best bet. Much easier to handle than partitioning the whole table, it offers similar performance for the split case and allows much better performance for queries on the whole table: CREATE INDEX tbl_nodelay_idx ON tbl (tbl_id, ??) WHERE delay <= 0; CREATE ...


0

Given the information in your comments there's not much you can do index-wise beside the index that you proposed. This index will have poor selectivity. Dependent of the size of the table and the update frequency of delay you might consider range partitioning, one partition for delay > 0 and one partition for delay <= 0. Instead of scanning the index and ...


7

Under the covers sp_indexoption just does an ALTER INDEX as well. My recommendation would be to forget about sp_indexoption and just utilize ALTER INDEX: alter index IX_YourIndex on dbo.YourTable set ( allow_row_locks = on ); go As noted in the BOL reference for ALTER INDEX, when you specify just the set options: Specifies index options without ...



Top 50 recent answers are included