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 ?

View 2 Replies


ADVERTISEMENT

Nonclustered Indexes Quickie

Jul 31, 2007

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.:)

View 5 Replies View Related

Scan Density Of Nonclustered Indexes

Oct 23, 1999

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?

View 2 Replies View Related

SQL Server 2012 :: Adding Flags Depending On Criteria

Feb 12, 2014

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:

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2)*($C$2:$C2>=C2-1/24)*($C$2:$C2< C2+1/24))> 1,0,1)
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

How I can do this in sql??

The columns above do not relate to the actual columns I use, just an example.

View 9 Replies View Related

SQL 2012 :: Filtered Indexes In Server

Aug 14, 2014

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

View 8 Replies View Related

SQL Server 2012 :: Indexes Based On A Query?

Aug 18, 2014

trying to create indexes on two tables:

SF_Affiliate_Customer
SF_Affiliate_Customer_Account

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

[code].....

View 3 Replies View Related

SQL Server 2012 :: Rule Of Thumb When To Disable Indexes?

Jun 19, 2014

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...

View 5 Replies View Related

SQL Server 2012 :: Script To Reorganize All Enabled Indexes

Jul 30, 2015

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?

View 5 Replies View Related

SQL Server 2012 :: How To Generate Index Creation Scripts (many Indexes)

Jun 24, 2015

Script they use to generate indexes in SQL 2005.

I have 2 databases on a separate instance. I want to script out all indexes from database1 then execute it on database2.

How to accomplish this task efficiently.

View 5 Replies View Related

Have Trace Flags Changed In SQL Server 7?

Dec 14, 1998

Have Trace Flags changed in SQL Server 7?
I need to use T204 because of getting error:

'ORDER BY items must appear in the select list if SELECT DISTINCT IS USED'
when I try to compile stor proc.

I entered T204 as startup parameter and restarted server
but doesn't seem to work.

Any experience with that?
Thanks

View 1 Replies View Related

Trace Flags Compatibility In SQL Server 2005

Sep 11, 2007

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.


Regards,

Chetan

View 1 Replies View Related

SQL Server 2014 :: Enable Trace Flags On A View

Aug 10, 2015

I need to enable trace flag

OPTION(QUERYTRACEON 9481)

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'.

View 3 Replies View Related

DB Engine :: Trace Flags 1117 And 1118 No More Enabled After Server Got Rebooted

Dec 1, 2015

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.

View 5 Replies View Related

SQL Server 2012 :: Why Indexes On Table Slow Down DML Operation On Table

Mar 7, 2014

Why the Indexes on table slow down the DML operation on table, what is the exact reason?

View 5 Replies View Related

SQL 2012 :: Create New Indexes

May 24, 2014

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.

View 9 Replies View Related

SQL 2012 :: Disadvantage Of Having Too Many Indexes

Aug 6, 2015

Any disadvantage of having too many indexes?

View 3 Replies View Related

SQL 2012 :: How Indexes Allocated On Pages

Nov 6, 2014

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?

View 4 Replies View Related

SQL 2012 :: Identify Unused Indexes In Table

Jan 15, 2015

How to find if there is a query that can be written on DMV's which will be able to retrieve the indexes that are not being used in a table.

View 2 Replies View Related

SQL 2012 :: Maintain Indexes When AlwaysON Is In Place

Aug 4, 2015

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.

View 8 Replies View Related

SQL 2012 :: Disabling Indexes For Load Performance?

Sep 22, 2015

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?

View 9 Replies View Related

SQL 2012 :: Way To Remove All Indexes From A Table With One Single Command

Sep 29, 2015

IS there a way to remove all indexes from a table with one single command ( without having to know what the index name is ) ;

View 2 Replies View Related

SQL 2012 :: Performance Limit On Number Of Indexes Per Table / Database

Oct 1, 2014

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.

View 4 Replies View Related

Nonclustered Index

Nov 19, 1999

hello!
Is it possible to set nonclustered
index on column with dublicate values?
Thank you,
Alona

View 1 Replies View Related

Clustered Vs Nonclustered PK

Jul 16, 2007

Ok, let me try to set the stage.



Between 2m and 5m inserts per day. NO UPDATES.



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)



Thoughts?

View 3 Replies View Related

Are Bit Flags Bad?

Mar 5, 2004

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.

Any insight would be appreciated.

Thanks

View 2 Replies View Related

SQL 2012 :: Maintenance Plan - Rebuild Indexes Only Run If Required Or Open Fires On Schedule?

Aug 23, 2015

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.

View 2 Replies View Related

CLUSTERED INDEX Or NONCLUSTERED

Feb 20, 2004

I have 3 table A, B, C

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

Thanks
Sorry for my english

View 5 Replies View Related

Clustered And Nonclustered Index

Jun 2, 2006

when do you use them?

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.

what about nonclustered?

View 4 Replies View Related

CREATE NONCLUSTERED INDEX

Jul 6, 2007

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 [ ].

Please help. Thank you.

View 7 Replies View Related

Composite Nonclustered Index

Jul 18, 2006

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 ?

Thanks

View 15 Replies View Related

Trace Flags

May 1, 2001

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.

View 1 Replies View Related

Can I Tell What Trace Flags Are On?

May 22, 2000

If I issue a DBCC TRACEON (1205) from a query window from a client machine.. Does it continue to run when I close that connection (spid).

Is there a way for me to tell what traceflags are currently on?

Thanks,

Dano

View 1 Replies View Related

Using Trace Flags

Sep 6, 2000

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.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved