Performance After Indexes
Sep 7, 2007how can we check the increase of performance after the creation of indexes
View 1 Replies
ADVERTISEMENT
Performance On Indexes With And Without Fillfactors
Mar 16, 2001Hi all,
Data load on a database without indexes(only nonclustered indexes on primary keys) took appx. 45 minutes.
Data load on the same database after creating many indexes (with default fillfactor of 0%)to improve query execution time took appx. 45 minutes.
Data load on the same database after creating many indexes (with fillfactor of 70%)to improve load time took appx. 90 minutes.
Can someone see the reason for this or justify this behaviour please?
Thanks in advance,
Praveena
Indexes: An Overview And Maintenance For Performance
Nov 30, 2004Many people know the importance of creating indexes on SQL Server database tables. Indexes greatly improve the performance of a database. However, while many people create indexes on their SQL Server tables, many people don't maintain them properly to ensure queries run efficiently as possible.
Thought you may be interested in a new article.
http://www.orcsweb.com/articles/index_overview.aspx
Thank you.
EAV Performance And Clustered Indexes On Views
Jan 25, 2008OK so I have this EAV system on a server that is old enough for kindergarten. Insanely enough, this company that makes more money than any of your gods can not buy me a new box.
Before you say "redesign", I need funding allocated for that. See my first statement.
Anywho, I have this page that touches the dreaded Value table and does a clustered index seek on it. Can't search faster than that, right? Well I am getting some funding for "performance tuning". I am wondering if maybe incorporating some clustered index views involving the value table and producing a smaller clustered index for it to seek may alleviate some of this. Any thoughts?
SQL 2012 :: Disabling Indexes For Load Performance?
Sep 22, 2015I'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?
Question About Performance Issues W/SQL2000 With NO Indexes
Jul 23, 2005hello,I've been assigned to do performance tuning on an SQL2000 database(around 10GB in size, several instances).So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate thelogs, database and OS(win2k).The one thing that was a bit odd to me was that I was told this placedoesn't use indexes. The company is a house builder. They are prettylarge.The IT manager isn't a programmer so she couldn't explain to me why noindexes are used. She told me the programmers just don't use indexes.Before I start investing more time on this, I'd really like to learnabout why you wouldn't want to use indexes - especially on such a largedatabase!Thanks,Oskar
View 9 Replies View RelatedCreating Indexes On Large Table To Increase Performance
Mar 5, 2008Dear all,
I'm using SQL Server 2005 Standard Edetion.
I have the following stored procedure that is executed against two tables (RecrodedCalls) and (RecordedCallsTags)
The table RecordedCalls has more than 10000000 Records and RecordedCallsTags is about 7500000 Records
Now the lines marked in baby blue are dynamic (Dynamic where statement) that varies every time this stored procedure is executed, may it contains 7 columns in condetion statement or may it contains 10 columns, or 2 coulmns.....etc
Now I want to create non-clustered indexes on the columns used in the where statement, THE DTA suggests different indexing whenever the where statement changes.
So what is the right way to created indexes, to create one index on all the columns once, or to create separate indexes on each columns, sometimes the DTA suggests 5 columns together at one if I€™m using 5 conditions, I can€™t accumulate all the possible indexes hence the where statement always vary from situation to situation, below the SP:
CREATE TABLE #tempLookups (ID int identity(0,1),Code NVARCHAR(100),NameE NVARCHAR(500),NameA NVARCHAR(500))
CREATE TABLE #tempTable (ID int identity(0,1),TypesCount INT,CallsType NVARCHAR(50))
INSERT INTO #tempLookups SELECT Code, NameE, NameA FROM lookups WHERE [Type] = 'CALLTYPES' ORDER BY Ordering ASC
INSERT INTO #tempTable SELECT COUNT(DISTINCT(RecordedCalls.ID)) As TypesCount,RecordedCalls.CallType as CallsType
FROM RecordedCalls LEFT OUTER JOIN RecordedCallsTags ON RecordedCalls.ID = RecordedCallsTags.CallID
WHERE RecordedCalls.ID <= '9369907'
AND (RecordedCalls.CallDate BETWEEN cast ('01 Jan 1910 00:00:00:000' as datetime ) AND cast ( '01 Jan 2210 00:00:00:000' as datetime ))
AND (RecordedCalls.Duration BETWEEN 0 AND 1000000)
AND RecordedCalls.ChannelID NOT IN('62061','62062','62063','62064','64110','64111','64112','64113','64114','69860','69861','69862','69863','69866','69867','69868')
AND RecordedCalls.ServerID NOT IN('2')
AND RecordedCalls.AgentID NOT IN('1000010000')
AND (RecordedCallsTags.TagID is null OR RecordedCallsTags.TagID NOT IN('100','200'))
AND RecordedCalls.IsDeleted='false'
GROUP BY RecordedCalls.CallType
SELECT IsNull(#tempTable.TypesCount, 0) AS TypesCount, CASE('English')
WHEN 'Arabic' THEN #tempLookups.NameA
ELSE #tempLookups.NameE
END AS CallsType FROM
#tempTable RIGHT OUTER JOIN #tempLookups ON #tempTable.CallsType = #tempLookups.Code
DROP TABLE #tempLookups
DROP TABLE #tempTable
Thanks all,
Tayseer
Any suggestions how to create efficient indexes??!!
SQL 2012 :: Performance Limit On Number Of Indexes Per Table / Database
Oct 1, 2014Is 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 RelatedIntegration Services :: Parameterized Bulk Copy - Disabling Indexes For Performance Causes Timeout Exception
Sep 23, 2015My requirement is to sling a rowset from one place in SQL server into a table in another place in the most performant way. I want this to be parameterizable - I want to provide just a connection string and some SQL for the source and a connection string and a table name for the destination. The package should do the rest.Â
The solution I chose was an 2014 SSIS package with source and destination as ADO.NET connections configured from project variables. The package has a script task to bulk copy the data. For performance I disable the non-clustered indexes first.Â
But this performance precaution causes the bulk copy to timeout after delivering the correct rowcount to the destination table. What I can do to avoid this error?
Here's my script code:
//get hold of the source and a data reader from it
SqlConnection sqlconnSource = new SqlConnection();
sqlconnSource = (SqlConnection)(Dts.Connections["source"].AcquireConnection(Dts.Transaction) as SqlConnection);
SqlCommand sourcesqlCommand = new SqlCommand(SourceSQL, sqlconnSource);
sourcesqlCommand.CommandTimeout = 1500;
[Code] ....
This takes 128 seconds to put 13 million thin rows into my empty destination table and then throws an exception with this message:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Removal Of Selected Indexes / Script Index Create For List Of Indexes
Jul 1, 2014I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
A Question About Clustered Indexes Forcing Rebuild Of Non-clustered Indexes.
Sep 18, 2007So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?
If so does this "pseudocode" example also cause this to occur:
sqlstring="select * from item where itemid=12345"
rs.open sqlstring, etc, etc, etc
rs.Fields("ItemName")="My New Item Name"
rs.Fields("ItemPrice")=1.00
rs.Update
Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?
SQL Server 2008 :: Logic To Rebuild Only Clustered Indexes / Skipping To Rebuild Non Clustered Indexes In Same Table
Jun 25, 2015I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),
[Code] ....
Indexes Vs Clustered Indexes
Sep 17, 2006What is the difference please?
View 1 Replies View Related[Performance Discussion] To Schedule A Time For Mssql Command, Which Way Would Be Faster And Get A Better Performance?
Sep 12, 20041. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection
above, which way would be faster and get a better performance?
Extremely Poor Query Performance - Identical DBs Different Performance
Jun 23, 2006Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies View RelatedVery Poor Performance - Identical DBs But Different Performance
Jun 22, 2006Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server witha particular query. It would take approximately 22 seconds to return100 rows, thats about 0.22 seconds per row. Note: I ran the query insingle user mode. So I tested the query on the Development server bytaking a backup (.dmp) of the database and moving it onto the devserver. I ran the same query and found that it ran in less than asecond.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue isrelated to some external hardware issue like: disk space, memory etc.Or could it be OS software related issues, like service packs, SQLServer configuations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating systemrelated issue.Any Ideas would help me greatly!Thanks,Brian T
View 2 Replies View RelatedIndexes
Jul 31, 2002Sir,
1. How do I call the indexes created in SQL server for a table from Front end VB programming ?
2. How can I use SEEK command with ADODB control ?
Please give me some tips & samples
Sundar Raman
Indexes
Jul 23, 2001Does anyone have the idea why the Indexes with _WA.....(like _WA_Sys_au_fname_07020F21) gets created.I don't how this index got created. I did not create this Index.
My Question is does the system creates these indexes or something else does this.
Thanks
Chak
DB Indexes
Jul 27, 2000Is there a way to tell how many indexes exist for an entire database, all I'm looking is for a count or generating a report list.
any help would be appreciated, thank you
Indexes
Sep 13, 2000At present I have been assigned to create indexes to retrieve the information fast, from the table. The existing table doesn’t have primary key, foreign key and unique constraints but I found to many default indexes already created by the system. I would like to know how this happened? Please inform how to delete these default indexes. Further, inform me other possible ways for the faster retrieval in SQL sever 7.0, if there are any.
I would appreciate if you send me a step by step explanations for the above problems.
Thanks a lot
Indexes Not Being Used
Nov 20, 2000I have run into a snag on my development server. Queries that are selecting data based on indexed fields in a where clause are using the wrong indexes. They are arbitrarily using the clustered index which isn't in the select at all and causing big performance problems. I can run the same statements on my production server and it runs based on the proper indexes. I used query execution plans to determine that this was infact the case.
I run DBCC Checkdb everynight and it comes back with no errors. I also rebuild the indexes. We also don't receive any other errors inputting or updating data. This sounds like corruption to me but if it's something else I don't want to spend the night restoring from production if there is another reason.
Has anyone encountered this before? Any ideas?
Appreciate it, K.
6.5 Indexes
Jan 10, 2001Is there any way for me to find out when last indexes have been used so that the one I don't need can be dropped.And also the one's that are of no use at all.
I need this as i am trying to dump all duplicated indexe . i know i can do this in ver 7
thanks
Indexes
Feb 5, 2000when executed sp_help tablename, I get lot of statistics and indexes like the following. Can anyone please tell me how it is generated automatically. as far i know statistics are generated only for primary keys. Can you please tell me what is clustered , hypothetical and the indexes starting with _WA supposed to be. Also there are lot of duplicate stats. Is it Ok to deletes those.
_WA_Sys_is_platinum_0A9D95DB
_WA_Sys_active_0A9D95DB nonclustered, statistics, auto create located on PRIMARY Active
hind_c_33_15 nonclustered, statistics located on
hind_c_37_1 clustered, hypothetical located
Thanks
Raj
Indexes
Mar 2, 2001Is there a way T-SQL script can find out all indexes built on a set of tables, drop them and periodically ( quarterly as an example ) re-build them ?
Thanks in advance for help.
Ivan
Indexes
Mar 5, 2001How do you find out indexes ( with column names info ) on a table ?
Thanks in advance.
Ivan
Indexes!
Jun 20, 2001I am on SQL 6.5.
I have a question about speed and indexes. I have a static table (no updates except once a year). I want to be able to search data quickly on one column or many columns. I have created nonclustered indexes on each of the columns I search by. Is there anything else I can do to speed up my queries? Unfortunately all the searches involve using the like operator. I have even broken my table down into 2 smaller tables (Table A ~ 3 million rows, Table B 8 million rows).
All suggestions will be appreciated.
Thanks,
Eric
The Indexes
Nov 12, 1998We have to interduce a new naming convention for the indexes currently available in the user databases.
We also have to drop all the old indexes available in about 250 tables and recreate them all acording to the naming convention we are coming up with.
Can any body suggest any idea.
I thank you guys in advance for your considaration.
Indexes
Mar 10, 2006hi.
there 2 different queries
1
[MYSQL].... where Cat=@CatID and Date=@Date[/MYSQL]
2
[MYSQL]... where Cat=@CatID and Date=@Date and Salesman=@SID[/MYSQL]
these queries are often used.
my question is about indexes.
should I use two different indexes?
index 1 : Cat,Date
index 2 : Cat,Date,Salesman
or only index 2 is enough ?
SQL Indexes
Jul 11, 2006I need to alphabetize PART of a union all query, and was told that this may be accomplished with an index. I tried joining two views, but it does not work! I have NO experience with indexes and need some help.
Here is my code:
Select id, country from countries where id = 6 union all Select id, country from countries where id <> 6
I want the country with id 6 (USA) to be the default on a drop down list, then all the other countries listed after it in alphabetical order. Can you do this with an index? If so, how do I proceed?
Thanks!
Ronna
All Indexes In DB
Nov 15, 2004Hi all,
I want to know all indexes in database.
What do I do to get them?
Thanks in advanced,
Thi Nguyen
Indexes
Feb 6, 2006We recently updated some of the databases from MS Access 2000 to SQL Server 2000. In Access we had columns which were set to Indexed No Duplicates, these were not Primary key fields. Is this possible to set some indexes to prevent duplicates in SQL Server without creating performance issues?
Miranda
Indexes
Feb 16, 2006Do you have to take the DB offline to create or run indexes in SQL Server 2000??
View 2 Replies View RelatedIndexes
May 30, 2007Q1
The table i have stores max 2000 records. Not sure if i neeed a clustered index for this few records. I guess not.
Q2
For the same table as above. How do you create primary keys if there is not unique records.
Do i create a new column? like newColumnID identity? and create a primary key on this new column? Ok even if i create this newcolumn. I don't think it will improve the retrieving speed cause i'm not selecting this column.
Opinions, ideas?