SQL Server 2012 :: Nonclustered Indexes On Bit Flags
Mar 24, 2015
I have a scenario where I have 3 columns and all 3 of them are used in the where clauses of simple queries or ones having joins .
TABLE(
Column1 int
FLAG1 bit
FLAG2 bit
)
Sample queries :
Select * from TABLE where FLAG1 =1 and FLAG2 =0
(Any combination of these flags)
Select * from TABLE inner join SOMEOTHERTABLE on
TABLE.Column1 = SOMEOTHERTABLE .Column1
where FLAG1 =1 and FLAG2 =0
( any join and combination of flags)
Questions :
What would be the best nonclustered index strategy :
Column1 as the index key including FLAG1 and FLAG2
or
Column1,FLAG1 and FLAG2 in the index key
Points to note :
The queries are part of an ETL process and are used to track new records vs old records. The Flags switch states within the same job . So if we are creating an index on all 3 columns, the index has to be reorganized more than once based on the flag states. If we keep them in the include list , then its only about updating the leaf data with the latest flag values.
On the other hand, an index on all 3 columns will result in an index Seek alone , where as for the included list , there will be an index seek and a predicate .
Does the predicate cause more overhead than reorganizing the index or is it the opposite ?
Hi You know when you reread something you've read many times and a penny suddenly seems to drop and you realise that you probably just never quite got it afterall? If creating a nonclustered index with no includes statement it is totally pointless to specificy a fillfactor other than 0 or 100 if you do not also use the PAD_INDEX option? Thought I'd make it a poll just for the hell of it.:)
A few days after droping and recreating noclustered indexes on a large table, scan density of most indexes falls below 20%. Only a few number of rows has deleted. Update operation didn't update these keys and there was a lot of inserts. Could anyone explain why are indexes fragmented so much?
I have a data output with many rows. In order to group things with flags, I do this in excel using 2 formulas which *** a flag of 0 or 1 in 2 new columns.
This takes a long long time as I have hundreds of thousands of rows and wondered of I could do it in sql?
Its transact SQL and the formulas I use in excel are:
What are driving criteria for creating filtered indexes on SQL server. I am trying to analyze the index stats through DMV,histogram and have to analyze if the filtered indexes should be created on tables. This exercise has to be done for all the transaction tables on the database. What are the approaches I should be looking on?
There was a deadlock on the DB because of huge writes on one of the big tables. Having filtered index on this table for the effected column would reduce the time taken for write operations. Hence we are looking for creating filtered indexes appropriately
on which the following query is based. I need to build indexes so that the query will perform better. Now its very slow..
SELECT DISTINCT C.[afflt_cust_natl_key],[as_of_dt] FROM [dbo].[SF_Affiliate_Customer] C WHERE ( [afflt_intrnl_cust_ind] = 'N' AND [afflt_empl_ind] = 'N' AND (ISNULL([phys_addr_st_rgn_cd],'')<>'CA' AND ISNULL([mlng_addr_st_rgn_cd],'')<>'CA') )AND
It's often said or done that when inserting or updating into a 'large' table that disabling the non-clustered indexes can is needed for performance.
Now I know the obvious way to find out if this is best or not is by testing the different options. I was wondering if there was a rule of thumb to this?
Say you have a table with half a billion rows and 4 non-clustered indexes and are only updating half a million rows then sometimes disabling every night and re-enabling can take way more time than the actual update. Haven't found an articles advising to disable them when a table is over X rows and you are updating Y% of them...
My index reorganise maintenance plan fails partly due to the disabled indexes
Executing the query "ALTER INDEX [I_ModelSecurityCommon_RECID] ON [dbo]...
" failed with the following error: "Cannot perform the specified operation on disabled index 'I_ModelSecurityCommon_RECID' on table 'dbo. Model SecurityCommon'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I don't want to delete the indexes as they are standard indexes that where on the DB from install.. any script that will reorganise all enabled indexes? and also to rebuild?
We have the following trace flags present in startup in SQL server 2000:
809 1204 3605 3913
Need to understand if these should be required in SQL Server 2005 + SP2 version. I have run the upgrade advisor tool which indcates that the behaviour of some flags has changed and some other TFs are no longer applicable. Hence, I want to know about the above mentioned TFs.
In one of my views I am having trouble finding where to put it in my existing statement:
USE [pec_prod] GO /****** Object: View [dbo].[PEC_Claim_Export_All] Script Date: 8/10/2015 9:18:26 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [dbo].[PEC_Claim_Export_All]
[Code] ....
Msg 156, Level 15, State 1, Procedure PEC_Claim_Export_All, Line 56 Incorrect syntax near the keyword 'OPTION'.
During a newly set up on one of our SQL server 2012:
We had enable the trace flags 1117 and 1118 as a good practice using DBCC TRACEON(1117,-1) and similar for 1118.
We have been base lining the server and it came to notice that trace flags are no more enabled.
Property               Value                              CaptureDate DBCC_TRACESTATUS TF 1117: Status = 1, Global = 1, Session = 0    2015-10-20 00:00:00 DBCC_TRACESTATUS     TF 1118: Status = 1, Global = 1, Session = 0      2015-10-20 00:00:00
After reboot: Property                 Value                                  CaptureDate DBCC_TRACESTATUS      No trace flags enabled             2015-10-21 00:00:02.340
What can be the reason? What can be done to turn them on permanently, if its actually a good bet in enabling so.
1)When we create Indexes, key columns are the columns that use in where clause and included columns are the columns that can be used in the select list and on join clause column.
2) I am thinking that we have to create new Index, only if we found at least 50 msec time save.
How Indexes are allocates on pages? And If a CREATE INDEX Statement Executed on a query Window, Query processor meets and executes these query. However it was meet, who decides to separate indexes onto pages? Storage Engine or Query Processor(Query Optimizer)? Does it work like UPDATE-Statements in Query Optimizer?
I have a new cluster (2 sync, 2 async) with about 50 databases going from 1 to 200gb ( all of the objects are compressed).That at sql server 2012, sp1 CU7.I have several drives for logs with 200gb of space in there...I am having issues at rebuilding indexes on this env, ie, I have a table with the clustered index heavily fragmented (~80%), and the table has about 60gb of data, uncompressed that should be about 160gb.
The index rebuild is creating a log file big enough as to consume all the space that I have for logs, and that is only 1 table, so for sure my old process to maintain indexes (ola.hallengren code) won't work on this scenario.
I'm trying to improve the loading of some tables with large amounts of data that forms part of an ETL. I was going to try removing any indexes before the inserting to speed up the process, but I had some questions on whether or not I should include the clustered index (assuming one exists).
I was originally planning on including a step to disable all indexes on the destination table using the following:
ALTER INDEX ALL ON MyTable DISABLE
Once the load had finished I'd simply rebuild all the indexes.
should I simply disable the non-clustered indexes?
Is there a performance limit on the number of indexes per table / database ? With Filtered indexes there appear to be many more opportunities for more finely defined, and therefore smaller indexes resulting in many more indexes on a single table.
Table has a 4 part primary key. All BigInt data types. Key value 1 and 2 have a range between 1 and 100. Key values 3 and 4 are auto incrementing values (forign key values) from other tables.
Space is an issue, so we have chosen not to have an additional column for a counter field for the PK. (We would never use the field for querying.)
Users complained of query speeds, so we added a couple non clustered indexs. This brought up the query speeds a lot. But of course it slowed down the input speed a bit. Nothing dramatic, but enough so we could tell.
Now the users was to increase the amount of data by about 5X. Obviosly I'm somewhat concerend, as SQL is already spending a lot of the day pegged.
So, in looking around, since the new indexes seem to be the most help in querying, I'm thinking of dropping the PK back to a nonclustered index, so I can get rid of the over head of restructring the data table on every insert. Then maybe making one of the other indexes the clustered index. (only 2 columns in this index)
Is the process of using integer data types to represent multiple values via the use of bit flags bad practice? It seems to go against the rules of normalization in a single field can represent multiple values. On the other hand that since these values can be tested for via bitwise operations, that it's not entirely bad.
Normally we use rebuild, reorganize indexes when it is required, I used a SQL job using maintenance plan to run daily and rebuild, reorganize indexes and update statistics but I do not know if it runs either they are required or not. Should this plan automatically execute the build upon required indexes to be rebuild or it fires either they are required to be executed or not.
Table A (15 field, 4 fields indexed and Primary Key) – approximate rows: 50.000 – 60.000
Table B (18 field, 6 fields indexed and Primary Key) – approximate rows: 350.000 – 500.000
Table C (16 filed, 9 fields indexed and Primary Key) – approximate rows: 500.000 – 1.000.000
Structure is something like this: A (master) --> B (detail) ---> C (sub detail)
On each 3 table is added new record, in table C the record is added after a search in table B. My question is: Which is the best method? CLUSTERED INDEX or NONCLUSTERED INDEX
i only know you use clustered when you have million of records. So once the table has been indexed, query statement is able to retrieve the recordset faster.
Hi guys. I have a table named [Check] and need to create an index for CVNumber field. The table has no primary key for the meantime. I tried this script but error occured.
BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON GO SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO COMMIT BEGIN TRANSACTION CREATE NONCLUSTERED INDEX IX_Check_1 ON dbo.[Check] ( CVNumber ) ON [PRIMARY] GO COMMIT
Error message:
Server: Msg 3023, Level 16, State 2, Line 3 Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed. Server: Msg 3902, Level 16, State 1, Line 1 The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Is it because I used the table name Check which is a reserved word for SQL? But I included [ ].
Hi everyone, I have some problems on composite nonclustered indexes. I could not exatly understand their logic. In my opininon, suppose that we have a table called Order and we create a composite nonclustered index on this table for OrderID column and OrderDate column. So I am using this query;
SELECT * FROM Order WHERE OrderID > 12 ORDER BY OrderDate So in here, I think our first research is based on OrderID and ten after ordering our data pointer according to the OrderID and then our index is converted to an index which is based on OrderDate while performing ordering. So is this correct ?? Would you please explain this ?
Hi , I want to trace the deadlock information. I am enabling trace flags 1204 ,1205. Is there any difference in setting these trace flags by using DBCC TRACEON and by setting them at command prompt by statrting the SQL server with SQLSERVR command. Actually i don't want to bring the server down. I want the information to be logged to Error log. Any help is greatly appreciated.
To solve a problem I encountered with Restoring from Backups in 6.5, I had to install a hotfix and thereafter do the load using Trace Flag 3282.I need help on using the trace flag (syntax etc.) Also there is no mention in books online of this particular Trace Flag. Please help.
