Tagged Questions

3
votes
2answers
36 views

Which non-clustered index should I have to use composite or single column in SQL Server?

I have 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
49 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
94 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
1answer
74 views

SQL Server: Sync Indexes between two tables within Same database

I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within same database. Tried using SSIS SQL Server Objects Task, but looks like it works only when we sync between ...
4
votes
1answer
128 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 ...
2
votes
1answer
122 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
72 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
180 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
68 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 ...
4
votes
2answers
186 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
417 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 ...
3
votes
1answer
174 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 ...
1
vote
1answer
209 views

Creating indexes with t-sql scrips vs rebuild indexes in maintenance plan

I’m using SQL Server 2008 and I am running several (15) scripts each day to bulk insert to tables. Each script drops the table at the beginning. I create indexes in the scripts. Some of the scripts ...
0
votes
1answer
68 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.
0
votes
1answer
118 views

SQL Server primary key create 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 ...
1
vote
0answers
62 views

Does disabling index exists in a table? [closed]

So I have a script that runs daily. It creates a temp table then inserts the records to a table from the temp table. The script used to create indexes but few months ago I commented out the index ...
3
votes
1answer
125 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
131 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 ...
0
votes
1answer
137 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 ...
3
votes
2answers
130 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
82 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
96 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 ...
3
votes
1answer
97 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 ...
2
votes
2answers
483 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 ...
5
votes
2answers
459 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 ...
1
vote
3answers
379 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, ...
3
votes
2answers
620 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
148 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 ...
5
votes
3answers
378 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 ...
2
votes
3answers
202 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 ...
5
votes
1answer
105 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
59 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. ...
2
votes
1answer
373 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 ...
6
votes
1answer
487 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%. ...
6
votes
1answer
279 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
106 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], ...
2
votes
1answer
214 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 ...
5
votes
2answers
121 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 ...
2
votes
1answer
239 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. ...
9
votes
1answer
701 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 ...
2
votes
1answer
240 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 ...
5
votes
2answers
105 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 ...
6
votes
1answer
489 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 ...
5
votes
2answers
369 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 ...
3
votes
2answers
393 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 ...
1
vote
1answer
229 views

Create Index with a WHERE clause

I've read the documentation on the MSDN, but it doesn't answer what I'm after. If I create an index on specified columns using a WHERE clause for certain IDs, this will limit the index to those IDs ...
2
votes
2answers
60 views

Best index for this scenario

There is a table in our database tblCustomText( ParentID int NOT NULL, CustomFieldID int NOT NULL, Value char(50) NOT NULL, Log_CreateDate datetime NOT NULL ) (ParentID+CustomFieldID) is ...
7
votes
2answers
674 views

Is there a way to force an index to stay in memory with SQL Server 2008?

I've got a table with several millions rows, from which I need to run some queries from time to time. First query will usually be quite slow (around 10s), and subsequent queries are usually way faster ...
4
votes
1answer
110 views

Filtered indices: Why include the filtered-on field?

This just drives me mad. Consider a simple table with irrelevant columns removed: create table boxes ( row_id int not null identity(1,1) primary key, location varchar(15) null, dismantled bit ...
3
votes
1answer
238 views

SQL Server: Duplicate Index Question

We recently has a massive performance problem on our system, which we narrowed down to a problem with I/O on the LUN. IOPS increased twenty-fold during the period when the problem was occuring. The ...

1 2