All Questions

Tagged with
Filter by
Sorted by
Tagged with
5
votes
2answers
881 views

If I update a column record in a table, will indexes that do NOT have this column in it be affected?

In terms of performance if I have a table like so: CREATE TABLE [TESTDATA].[TableA]( [Col1] [nchar](5) NOT NULL, [Col2] [nchar](2) NULL, [Col3] [float] NULL CONSTRAINT [TableA_PK] PRIMARY ...
7
votes
1answer
1k views

How to set SQL Server index pages per fragment?

I have SQL Server 2008, and a number of databases. I have discovered that one of my table's indexes is extremely fragmented (How I know: http://msdn.microsoft.com/en-us/library/ms189858.aspx) The ...
8
votes
3answers
5k views

Dropping Hypothetical Indexes

In the past I thought I'd deleted hypothetical indexes using either a DROP INDEX statement for clustered indexes and DROP STATISTICS statement for non-clustered indexes. I have a database that is ...
5
votes
2answers
2k views

Should I use a composite or single-column index?

I have the following columns in my database table (Medicines). ID bigint, MedicineName nvarchar(50), BrandName nvarchar(50), MedicineCode nvarchar(20), and price,quantity. I am making a stored ...
0
votes
1answer
112 views

Can I use the database and query it while full-text indexing?

I need to index my database, but I need to query it while it is indexing, it may take a long time, can I do that?
1
vote
2answers
5k views

Auto-generate scripts to create tables, indexes, etc

In SSMS, we can right click a database/table/index/... and select the "Script As" option to generate a drop and create script. Is there a way to automate this script generation and drop the scripts ...
0
votes
2answers
1k views

Sync Indexes between two tables

I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within the same database. I tried using SSIS SQL Server Objects Task, but looks like it works only when we sync ...
4
votes
1answer
841 views

Indexes file damaged, was on ramdisk

I have put some of my indexes into a filegroup that contains one file, that file is on the ramdisk, the performance is great, but the problem is that the file was deleted incorrectly (the file ...
6
votes
2answers
6k views

Bitmask Flags with Lookup Tables Clarification

I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries ...
2
votes
2answers
5k views

Differences Between Two Different Create Index Commands

Are there differences between these two scripts? Or would all of the extra tokens/attributes (ie: NONCLUSTERED, WITH..., etc...) for the 1st script be defaults in SQL Server 2008 for the 2nd script? ...
6
votes
1answer
410 views

Landed as BI, but databases are a big WTF, what to do?

Maybe a duplicate, but I believe my case is a bit different. From one of the answers I got to this post on SQL Server Central that also comes handy too but is not quite the same scenario: 9 Things to ...
5
votes
1answer
575 views

RESOURCE_SEMAPHORE_QUERY_COMPILER waits in Microsoft SQL Server 2008 Enterprise

Could fragmented indexes on Microsoft SQL Server 2008 Enterprise cause RESOURCE_SEMAPHORE_QUERY_COMPILER waits? The last two weeks one of our applications has had downtime due to ...
18
votes
2answers
21k views

What are the differences between leaf and non-leaf pages?

I've been running some index usage reports, and I'm trying to get a definition of Leaf and Non-leaf. There seem to be both Leaf and Non-leaf Inserts, Updates, Deletes, Page Merges, and Page ...
4
votes
1answer
1k views

SQL Server 2008 query planner failing after index drop & recreate

Recently we ran a script to our production DB that would dynamically drop and recreate hundreds of indexes as filtered indexes. While this script had run perfectly in all other previous tests, now ...
6
votes
1answer
3k views

Schema design: Use of association (aka: bridge/junction) table vs foreign key constraint with composite index containing a non-key field

This is an inventory database for IT assets. The models used are trimmed in order to focus on the problem at hand. Using SQL Server 2008. Thanks for taking the time to read and for any input you can ...
0
votes
1answer
383 views

What is the maximum number of rows in a clustered index on datetime column?

I would like know what the max no of rows is in a clustered index (non-unique) on a datetime column table in SQL Server 2008R2.
2
votes
1answer
188 views

Will a primary key be added as a clustered index?

I've inherited a database where no primary keys were defined on the tables. There are also no clustered indexes assigned to the tables. If I perform an alter table to assign a primary key will this ...
3
votes
1answer
2k views

Index Key Column Order

I have created a joining table for many-to-many relationship. The table only has 2 cols in it, ticketid and groupid typical data would be groupid ticketid 20 56 20 87 20 ...
1
vote
3answers
651 views

When to create Indexes?

I have a stored procedure that takes around 2 minutes to execute. The execution plan suggested me to create a non clustered index on a table (which is a high traffic table with millions of records in ...
6
votes
1answer
3k views

How much of a view is persisted when you create an index?

Say I have a view defined by the following SQL: SELECT t1.id, t2.name, t3.address FROM Table1 as t1 INNER JOIN Table2 t2 ON t1.id = t2.tID INNER JOIN Table3 t3 ON t1.id = t3.tID From what I ...
4
votes
2answers
11k views

Find fulltext indexes in SQL Server 2008

How do I find a list of tables that have a FULLTEXT INDEX applied to them on a given database? For example, you can enable and disable a FULLTEXT INDEX like this: ALTER FULLTEXT INDEX ON [dbo].[...
3
votes
2answers
192 views

Proper Index for WHERE X <> 1 AND Y <> 1 ORDER BY Z

I have a relatively large table that is accessed in exactly one way, by exactly one query (on the read side). The query filters the table by two columns (both bit), and sorts by a third column (an ...
-1
votes
1answer
966 views

Covering Index isn't being used and I don't know why

We have this complicated query that I'm trying to make "better" until we move it to pull from a data warehouse. I need a solution that's "good enough" for now and I think I'm about 2-3 indexes away ...
3
votes
2answers
586 views

Why is this key lookup required?

I have an admittedly-complex query that I'm optimizing and I'm stuck on the next step. I have pinpointed the bulk of my problem to one part of my query that's doing the following Key Lookup but I'm ...
4
votes
1answer
343 views

How to update views outside of update/insert transaction in SQL Server

Looking at SQL Server indexed views I see that these views are updated within a transaction that updates dependent tables. I wonder if there is a way around it? There will be about 10 views with 20 ...
4
votes
3answers
7k views

SQL Server Timeout: Drop and Recreate Index Solves the Issue

After having sql timeout issues with one of our queries we added a few indices that then made the query run very quickly and got rid of any timeout out issues we were having. The strange thing is ...
2
votes
2answers
2k views

IN Clause causes Execution plan to change from Nested Loops to Hash Match

I'm tuning a query and have discovered some behaviour I'm not clear about. If I remove the WHERE IN clause the query runs in 3 seconds instead of 3 minutes. There only 7 rows returned in the ...
7
votes
2answers
7k views

Best value for fill factor in index

I use SQL Server 2008 R2 and want to set value for fill factor property in each index. I look for recipe to calculate best value for this property (have better performance when insert record) . Also ...
2
votes
3answers
2k views

Improve performance of a Fact Table

I have a Fact table CardTransactionFact Table Structure TABLE [dbo].[CardTransactionFact] [CardTransactionID] [int] IDENTITY(1,1) NOT NULL, [TransactionTerminalID] [int] NOT NULL, [...
6
votes
2answers
3k views

Rebuilding index has impact on mdf/ldf files SQL Server 2008

While rebuilding index have used option 'SORT_IN_TEMPDB = ON' in order to avoid unnecessary growing your user database files. What does it exactly means? Is complete process done on TempDB and does ...
8
votes
2answers
215 views

Non-Clustered Indexes - keys and nonkeys

I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated. Here's my theory from the query I've just optimised, through a process of trial and ...
6
votes
3answers
3k views

Does having an index on a VARCHAR column with a lot of very similar starting values have bad performance

We seem to be having quite unusual bad performance on queries that use an index. for example the table looks like PK BIGINT ID VARCHAR(50) Col1 Col2 etc So we need to insert a row in the ...
4
votes
3answers
1k views

SQL Server : explicitly create an index on a primary key and unique fields

UPDATED: I'll make the question clearer as the first answer isn't quite what I was looking for. How can I execute this create table statement and ensure that the two automatically created indexes ...
3
votes
4answers
1k views

How does the database decide which Index to use

DDL create table t ( id int, id1 int ) create index Example_Index On t(id,id1) create index Example1_Index On t(id1,id) DML insert into t(id, id1)values(1, 100) insert into t(id, id1)...
6
votes
1answer
2k views

SQL Server: Effect of Setting These Index Options

A reindexing script was recently run in our dev environment that set ALLOW_ROW_LOCK = FALSE and ALLOW_PAGE_LOCKS = FALSE on all the indexes in the database. This was done while testing new reindexing ...
1
vote
1answer
112 views

Where does it make sense to store Indexes for partitioned tables?

Prior to table partitioning, all the indexes were stored on a separate drive, per this question. Now that I partitioned a couple of tables, SSMS is offering to store indexes in the partition scheme. ...
5
votes
1answer
3k views

creating an index for varchar column greater than 900 bytes

I have a var char column in SQL Server 2008 which is usually less than 100 bytes but in some cases can be huge up 5607 bytes. I need to index it, and I can't because the max index size in SQLServer is ...
11
votes
2answers
3k views

How to prevent daily index fragmentation of 99%

I have a highscore table for 100.000 players that is being inserted into 2 times a day with one record per player. At the end of the day the index fragmentation for the indexes in that table is 99%. ...
7
votes
1answer
1k views

computed columns, index, clustered index and covering index?

We all know the we can only have one clustered view per table, cool. But apparently you don't have values of computed columns on leaf even when using a clustered index and they are computed each time. ...
1
vote
1answer
163 views

SQl code performance

What measurement do you know if this sql code is efficient based on execution plans? How do you know if this sql code is efficient based on execution plans? SELECT [CustomerID], [CompanyName], [City],...
8
votes
2answers
14k views

Tools for Identifying Needed Indexes

I want to create the best indexes for each table in my database. Is there a query or tool in SQL Server to help with this process?
2
votes
1answer
2k views

Disabling Indexes and Auditing for large data import

Hopefully the title describes this sufficiently, but I have a process that runs and performs thousands of individual inserts and updates (nothing I can do to change that). My questions are: 1. Is it ...
7
votes
2answers
209 views

In SQL Server, is it possible to have a PRIMARY KEY on a table without either CLUSTERED or NONCLUSTERED indexes on the same key?

In Sql Server (2008), is it possible to have a PRIMARY KEY on a set of columns without either CLUSTERED or NONCLUSTERED indexes on the same set of columns? I am aware of the fact that PRIMARY KEY and ...
3
votes
1answer
936 views

Greater than operator ignoring a nonclustered index?

I have a pretty simple query in SQL 2008 R2 and I have a nonclustered index on the "sDate" datetime column. When I look at the execution plan for the following query it seeks the nonclustered index. ...
17
votes
1answer
10k views

When should you specify PAD_INDEX?

So, you can use FILLFACTOR to leave space in leaf index pages. Specifying PAD_INDEX also leaves space in intermediate nodes. What scenarios should you specify PAD_INDEX, and what benefit does it ...
3
votes
1answer
4k views

How can I set the index fill factor back to server default?

I have some indexes that I was playing with on my dev server. I set the fill factor of these indexes using the following command: alter index all on dbo.Table rebuild with (fillfactor=80) That sets ...
5
votes
2answers
206 views

Are existing entries immediately included into a newly created index?

Suppose I have a table Stuff with a column City and no indices including that column. I populate the table. Then I decide to create an index: CREATE INDEX [StuffOnCityIndex] ON [dbo].[Stuff](City ASC)...
6
votes
1answer
1k views

Are there any performance benefits to using a hash table with no clustered index?

I have this table: CREATE TABLE [dbo].[relatea] ( [mid] [varchar](16) NOT NULL, [sid] [varchar](16) NOT NULL ) It stores hash matches. Is there any benefit to having this as a heap? It has ...
6
votes
2answers
7k views

Dropping and recreating indexes for performance reasons?

I'm maintaining Somebody Else's Code. There's a stored procedure that's run once a day or week (depending on config), that migrates a bunch of records from an active table to an archive. But it's ...
4
votes
2answers
1k views

Performance degradation after rebuilding indexes

I am optimizing some server application task that uses database (querying, complex calculations, data insertions...). Execution of this task spends about 16 minutes (I have tested it 3 more times) and ...