Sql 2005 - Optimization - Cannot Use Index
Jan 26, 2007
I have a very very strange situation with a particular application and sql
server 2005 enterprise.
This application combines numerical data from multiple tables. User can make
query over this kind of tables, can build queries with "group by" "order by"
and "join", "sum, count(*) ecc. on many many columns.
I cannot know the query that application is going to build, so I do not know
how to create indexes.
I cant make indexes on all the columns of course, so I'm creating some index
over columns that should be statistically used in the join, but when there
is a group by on a column chosen from the user, I realize that the plan
become non efficient with the famigerate "TABLE SCAN".
Can somebody, give me an idea, to optimize this situation.
Thanx
Massimo
View 4 Replies
ADVERTISEMENT
Apr 10, 2008
Hello Everybody,
I have a small tricky problem here...need help of all you experts.
Let me explain in detail. I have three tables
1. Emp Table: Columns-> EMPID and DeptID
2. Dept Table: Columns-> DeptName and DeptID
3. Team table : Columns -> Date, EmpID1, EmpID2, DeptNo.
There is a stored procedure which runs every day, and for "EVERY" deptID that exists in the dept table, selects two employee from emp table and puts them in the team table. Now assuming that there are several thousands of departments in the dept table, the amount of data entered in Team table is tremendous every day.
If I continue to run the stored proc for 1 month, the team table will have lots of rows in it and I have to retain all the records.
The real problem is when I want to retrive data for a employee(empid1 or empid2) from Team table and view the related details like date, deptno and empid1 or empid2 from emp table.
HOw do we optimise the data retrieval and storage for the table Team. I cannot use partitions as I have SQL server 2005 standard edition.
Please help me to optimize the query and data retrieval time from Team table.
Thanks,
Ganesh
View 4 Replies
View Related
Mar 28, 2008
Hi , How to optimize sql query in sql server 2005, any idea
View 4 Replies
View Related
Jan 7, 2006
I posted this on the .NET Framework inside Sql Server forum as well. Sorry if the cross-post offends anybody.
I upgraded my primary production server this morning to SQL 2005. Everything went fairly smoothly, but a couple of hours after my installation was complete, I found the following error in my event log:
Source: .NET Runtime Optimization Service
EventID: 1101
.NET Runtime Optimization Service (clr_optimization_v2.0.50727_32) - Failed to compile: Microsoft.ReportingServices.QueryDesigners, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 . Error code = 0x80070002
I am a little stumped since we did not install Reporting Services. We only installed Database Services, Integration Services and Workstation Components. I'm open to any suggestions on this. This does not seem to be negatively affecting our server, but I do want to resolve it as soon as possible.
Thanks,
Kevin
View 47 Replies
View Related
Oct 20, 2006
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
thank you in advance
View 3 Replies
View Related
Jan 21, 2008
We have been using Sql Server 2005 Compact Edition 3.1 RDA synchronization method successfully on Sql Server 2000 database. Recently we moved the database to Sql Server 2005, sync doesn't work anymore, it just hangs on one table. On further investigation, we found out that it's the index on that table that causes this. We removed the index, it works fine. We are wondering the root cause, removing the index is not a solution for us. Any thoughts?. Thanks.
View 1 Replies
View Related
Mar 21, 2007
how do you create an index in SQL over the Student table to lookup for example by last name and what is the syntax for the SQL lookup statement.
View 1 Replies
View Related
Jan 30, 2008
Is there a simple way to find properties of an index for a table? Specifically I am looking for the unique property. I currently have code to to do this, however I feel like there must be some easier way.
I am using .net 2005 and SQL 2005 coding in c#. Thanks for any help!
Code Snippet
_conn.Open();
SqlCommand cmd = new SqlCommand("sp_helpindex", _conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@objname", "TABLENAME"));
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(results);
foreach (DataRow row in results.Tables[0].Rows)
{
ArrayList names = new ArrayList();
DataTable dtUnique = new DataTable();
SqlCommand cmd2 = this._conn.CreateCommand();
cmd2.CommandText = String.Format("SELECT INDEXPROPERTY(OBJECT_ID('TABLENAME'), 'INDEXNAME', 'IsUnique') AS isUnique");
dtUnique.Load(cmd2.ExecuteReader());
bool unique = Convert.ToBoolean(dtUnique.Rows[0]["isUnique"]);
if (unique)
names.Add(tableName);
}
_conn.Close();
View 9 Replies
View Related
Jul 24, 2006
Hi,
Does anyone here know of any on-line references on how to optimize index maintenance in sql 2005? Also do you know of any good DBA books that will explain database maintenance and or best practices?
Thanks.
:D
View 2 Replies
View Related
Nov 28, 2006
I ran the DBCC SHOW_STATISTICS command for all of my indexes; I was told that high density numbers are bad, low numbers good. I have some questions about my results, though; I'm not sure how to interpret them.
Of 48 indexes, 14 have a density of 0. Does this mean that the indexes are not selective enough? Does it mean they're garbage and I should toss them?
6 have a density of NULL. They are all primary keys. I suppose this just means that they're never used because these tables are rarely queried. Would this assumption be correct?
13 have a density of 1. I have no idea what this means.
The others have densities ranging from 0.01210491 to 0.5841165. I was told that the lower this number is, the more selective and thus more useful an index is. I think 0.5841165 is too high a number. Would this be correct?
Thanks in advance.
View 14 Replies
View Related
Dec 4, 2007
SQL Server 2005 version: 2153
I created a maintplan for system and user databases includes rebuild index, maint cleanup tasks.
Job is failing for user databases
It includes rebuild index task( online index enabled) and maintenance cleanup task, scheduled at every sunday 1 AM.
I receive following errors:
In eventvwr log
sql server scheduled job 'DBMP_RebuildIndex_User'
status: failed-Invoked on 2007-12-02 -1:00 Message: The job failed. The job was invoked by schedule 8 ('DBMP_RebuildIndex_User-Schedule).The last step to run was step1 ('DBMP_RebuildIndex_User')[/red]
In log report:
Failed:(-1073548784) Excuting the query "ALTER INDEX [XPKact_log] ON
[dbo].[act log] REBUILD WITH (PAD_INDEX=OFF,
STATISTICS_NORECOMPUTE=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON,SORT_IN_TEMPDB=OFF,ONLINE=ON)
"failed with the following error "Online index operation cannot be performed for index 'XPKact_log' because the index contains column 'action_desc' of data type text, ntext.image.varchar(max),varbinary(max) or xml. For non clusterd index the column could be an include column of the index. for clusterd index it could be any column of the table .Incase of drop_existing the cloumn could be part of new or old index. The operation must be performed offline". Possible failure reasons : Problems with the querey .'" Resultset" property not set correctly, parameters not set correctly, or connection not established correctly.
Please anyone help me on this?
I really appriciate
Thnks
View 1 Replies
View Related
Apr 2, 2007
I would like to have some clarification about index-related recommendation from Database Tuning Advisor.
Let me describe the scenario first:
There is a table with clustered index defined on ID column of type INT and there are other columns of varchar/int types as well. Now when I run tuning advisor I get recommendations related to creating statistics as well as non-clustered indexes. When I view the syntax for recommended non-clustered index, sometime it explicitly add ID column as well which already has clustered index defined on it. e.g
CREATE NONCLUSTERED INDEX idx_TableName_IndexName ON dbo.TableName
(
ColName1 ASC,
ColName2 ASC,
ID ASC
)
My understanding is that for each non-clustered index, clustered index is automatically a part of it and that is how non-clustered index retrieve the actual data. I have seen it more often than not in DTA's recommendation to include clustered index column somewhere among columns for indexing for so many of my tables.
I can understand if the recommendation was to INCLUDE clustered-index column.
I would appreciate if someone out there could help me to understand what I am missing here.
View 3 Replies
View Related
Oct 30, 2006
After upgrading a server from SQL Server 2000 to 2005,
an index was suddenly being reported as corrupt in the SQL Server log
(probably every time an Insert was done). DBCC CHECKDB / CHECKTABLE
reported no errors, with or without the new WITH DATA_PURITY option.
Anyone else experienced something similar?
How can an index be reported as corrupt when DBCC doesn't report it?
Dropping and re-creating the index solved the problem.
I've restored a backup of the database (made before dropping/re-creating
the index) to try repeating the problem, but no success so far.
View 1 Replies
View Related
Mar 28, 2006
When one process has one record locked in Update-Mode then an other process can't update any other records on that table with some queries, other queries that access the same records (but with a different WHERE statement) will execute.
This problem occurs with SQL Server 2005, but it didn't with SQL Server 2000 (or any other database).
The problem:
-Process 1 locks a record in the table, and keeps it in Update-Mode, because the user is editing it. (Using OLEDB Pessimistic Cursor-locking)
-Process 2 wants to update an other record, buts gets a "Lock timeout" when using one query but not with another.
For example this query will work :
UPDATE gwseqnumber SET nextseqnr = 3 WHERE row_id = 110;
But this qeury will give me a "Lock timeout" :
UPDATE gwseqnumber SET nextseqnr = 3 WHERE name = 'REC_2';
But it is the same record!!
The record with name = 'REC_2' has the row_id = 110, both values are unique in the table.
The data:
The table [gwseqnumber] has the following CREATE statement:
CREATE TABLE GWSEQNUMBER
(
NEXTSEQNR INTEGER,
NAME CHAR (20),
ADMINISTRATIONCODE INTEGER,
FINDHIGHESTNUMBER CHAR (1),
CLOSEDYN CHAR (1),
ROW_ID INT IDENTITY(1,1) NOT NULL
);
CREATE INDEX KEY_1 ON GWSEQNUMBER (NEXTSEQNR);
CREATE UNIQUE KEY_2 ON GWSEQNUMBER (ADMINISTRATIONCODE, NAME, IDENTIFIER);
CREATE UNIQUE INDEX KEY_3 ON GWSEQNUMBER (ROW_ID);
Both KEY_2 and KEY_3 are unique, KEY_1 is not.
If I remove the index on the NEXTSEQNR column (the index named KEY_1) then both these queries will work, so it is obviously related to the index.
Altough the index is obsolete and can be removed from this table, it should not result in bogus locking errors.
Even when i removed the index not all the queries will work. (With work i mean not run into a locking error, while the record is not even locked.)
Solutions tried:
- Set the compaitiblity level of the databsae back to 80 (for SQL Server 2000 compatibility).
- I have already tried to disable Page-Locking on all the indexes of this table.
So why does the index lock my updates in SQL Server 2005?
And how do i fix it so my database does not run into these locks?
View 14 Replies
View Related
Mar 27, 2008
Hi,
I have a problem restoring a Read-Only filegroup in a database.
I've restored the Read/Write filegroups first without any problem.
Then, I am able to recover Read-Only filegroups which do not contain fulltext indexes with the command :
RESTORE DATABASE GSM FILEGROUP = 'FG_RO_20080214' WITH RECOVERY
However, for filegroups containing FullText indexes (Fulltext indexes are in the same filegroup as data used for this index), I always get the following error :
Server: Msg 3149, Level 16, State 6, Line 1
The file or filegroup "sysft_CAT_SM_20080127" is not in a valid state for the "Recover Data Only" option to be used. Only secondary files in the OFFLINE or RECOVERY_PENDING state can be processed.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
If I'm looking at sys.database_files catalog view, I can see the following
name type_desc state_desc
------------------------ ----------- -----------------
sysft_CAT_MS_20080127 FULLTEXT RECOVERY_PENDING
Does anyone have an idea, please.
Thanks in advance
Patrick
View 2 Replies
View Related
Jan 2, 2007
Info: CE.Net 4.2
CF 2
SQL Server 2005
First of all, sorry for the cross post but i need a quick answer on this.
I am executing a CLR Stored Procedure from a Windows Mobile device and regularly get a general network error.
The stored procedure cycles through up to 70 tables, checking for any records between two dates. The LastUpdate field is in each one of the 70 tables and is indexed.
The mobile device regularly reports general network error, which is due to a timeout. Command timeout is now set to 10 minutes but the timeout still occurs.
The stored procedure appears to get stuck on a particular table. If I rebuild the index on this table, it will get past it but then get stuck on the next table.
I set up a maintenance plan to run every 5 minutes (extreme, I know!) but the indices would appear to corrupt (without any activity on the table) in less than this interval.
However, running the exact same query from SQL Server Management Studio works correctly every time and usually within 10 seconds.
Im sure that it has something to do with the indices but cannot seem to resolve the issue. Its even more confusing how only the mobile device is affected!
Any help or advice would be greatly appreciated.
View 3 Replies
View Related
Oct 26, 2006
Greetings,
I have two tables:
CustomerOrder
----
ID
CustomerID
StatusID
CustomerOrderDetail
----
ID
Order_ID
StockID
Quantity
CustomerOrderDetail table has clustered unique index for ID and non-clustered for Order_ID
SQL Server 2005 is using table scan for CustomerOrderDetail table When I user the following query:
select
cod.*
from CustomerOrder co
inner join CustomerOrderDetail cod ON cod.Order_ID = co.ID
where
co.StatusID = 8 -- Pending
Both of the tables are pretty big, detail table has more than million records, so scanning the table is a bad idea.
When I specify hint to use index then sql seeks, but how do I make SQL server to use index automatically? I don't want to use hints in my queries.
Thanks!
View 4 Replies
View Related
Mar 13, 2008
I know how to create it from the query window:
CREATE FULLTEXT INDEX ON table_name
[(column_name [TYPE COLUMN type_column_name]
[LANGUAGE language_term] [,...n])]
KEY INDEX index_name
[ON fulltext_catalog_name]
[WITH
{CHANGE_TRACKING {MANUAL | AUTO | OFF [, NO POPULATION]}}
]
But where and how can I create it graphically in management Studio for 2005?
Thanks for any help or information.
View 1 Replies
View Related
Aug 9, 2006
Hi,
When I create a unique constraint, SQL Server automatically creates an index on this constraint. So when I run the following...
ALTER TABLE PersonsProjects
WITH NOCHECK ADD CONSTRAINT NoDupes UNIQUE NONCLUSTERED (PersonID, ProjectID)
...SQL Server will create a composite index on PersonsProjects called NoDupes on PersonIDand ProjectID. Thing is, I need this index to include a third column Status since most queries use this column in conjunction with PersonID and ProjectID. If there was no index on this table, I would have created it as follows:
CREATE UNIQUE INDEX NoDupes ON PersonsProjects (PersonID, ProjectID) INCLUDE (Status) WITH IGNORE_DUP_KEY
But this won't enforce the unique constraint on PersonID and ProjectID when performing inserts and updates. Is there any way of creating a unique constraint with an included column?
I would rather not have two indexes...
NoDupes: PersonID,ProjectID
New Index: PersonID,ProjectID INCLUDE Status
...so I'm trying to determine what other options that might be available...please advise.
Thanks much.
View 10 Replies
View Related
Jun 3, 2006
Here is an extract from the Acc2Sql2.doc >>
By default, the Upsizing Wizard
transfers all indexes as nonclustered indexes. You can modify the Upsizing
Wizard to transfer the primary key index to a clustered index. To make this
change, start Microsoft Access and open the upsizing wizard library database.
For Microsoft Access 95, the filename is Wzcs.mda. For Microsoft Access 97, the
filename is Wzcs97.mda. When the database is open, click the Modules tab and open the
UT_ModUserConstants module. Search down to the UT_CLUSTERED constant. Change
the default value from False to True.
<<< end quote
Question: I am using ACCESS 2002 upsize wizard, I searched my computer for *.mda and could not find any wzcs*.mda. How do I set it so that it creates clustered-index instead of non-clustered-index?
View 1 Replies
View Related
Nov 18, 2007
Hi,
I have visual studio 2005 with the sql server express edition that is installed by default when installing vs2005.
I am used to work from within vs when I want to create and work with databases for my web sites (I dont have visual manager installed).
I would like to enable full text search on a table's column.
Is there an easy way to do that from within vs or is it necessary to install another copy of sql server express with advanced services as well as sql server managment studio express?
How can I know if full text search is enabled?
Thanx in advance!
View 1 Replies
View Related
May 24, 2007
I am attempting to move a timestamp data column from DB2 to SQL Server 2005. Normally not a big deal but the column is part of unique index.
The DB2 timestamp has seconds of ss.ssssss but SQL Server only has ss.sss.
Most all the times entered into this column are a from an automated process so they are really close together timewise.
Here is what I have come up with so far:
1. Fast Load OLEDB with a batch of 10,000 records at a time
2. On the fail of the batch redirect rows to a regular table load OLEDB insert task
3. On the fail of the single insert redirect rows to script that ups the seconds one tick.
4. Attempt one last insert of the modified rows
5. If fail, then store the record off to a delimited text file
I am hoping to get the number of records that wind up in the delimited text file to be a very small number and not in the 1,000+.
Any help would be appreciated.
View 5 Replies
View Related
Apr 12, 2007
Hello..
When I used Microsoft SQL Server 2005 Management Studio Express to Create FULL TEXT INDEX by this code:
CREATE FULLTEXT INDEX ON txtfilestbl(txtfile) KEY INDEX PK_txtfilestbl ON ForumsArchiveLibCtlg WITH CHANGE_TRACKING AUTO
It returns this ERR MSG:
Informational: No full-text supported languages found.
Informational: No full-text supported languages found.
Msg 7680, Level 16, State 1, Line 1
Default full-text index language is not a language supported by full-text search.
I Use same this code to create FULL TEXT INDEX by using Microsoft SQL Server 2005 Management Studio, and it was working properly.
What I have to do?
View 2 Replies
View Related
Jun 6, 2007
Dear Advance,I used one stored procedure to retrive 3 different result set. and in the codebehind i seperate it. means from the dataset i seperate three different datatable and then show my data as my need.but the main problem is ... after retriving the datafrom the database i have to user foreach loop to bind the coulmns data to my different custom class.example: foreach (DataRow oDrow in MyDataTable.Rows) {oClass=new Class();oClass.Name1=oDrow["Name1"] .toString();oClass.Name2=oDrow["Name2"] .toString();.... } 1. so my first question is there any optimization possible ?2. my result set is too loong ... so should keep just one hit to database or hit more than one time Currently i am optimizing my web application. in the previous version 1 have to hit the database 3/4 times for different purposes. but now it hits only one time... but it takes time in the codebehind to perform different operation.Any Suggestion
View 1 Replies
View Related
Dec 11, 2001
I have a SP that calls about 10 stored procedures sequentially. The 10 SP's are basically complex update statements, each one individual. Is there any way to optimize this?
I know putting the 10 into 1 SP would make it compile faster but thats about it. Are there any execution tricks of Stored Procedures firing off sequentially?..or anything I should know?
-thanks
Bartman
View 2 Replies
View Related
Mar 2, 2001
Hello All,
What is the best way to optimize this code or rewrite it using ISNULL ?
CREATE PROCEDURE get_employees (@dept char(8), @class char(5))
AS
IF (@dept IS NULL AND @class IS NOT NULL)
SELECT * FROM employee WHERE employee.dept IS NULL
AND employee.class=@class
ELSE IF (@dept IS NULL AND @class IS NULL)
SELECT * FROM employee WHERE employee.dept IS NULL
AND employee.class IS NULL
ELSE IF (@dept IS NOT NULL AND @class IS NULL)
SELECT * FROM employee WHERE employee.dept=@dept
AND employee.class IS NULL
ELSE
SELECT * FROM employee WHERE employee.dept=@dept
AND employee.class=@class
Thanks in advance.
-Praveena
View 1 Replies
View Related
Jul 6, 2001
I am wondering if the size of the data file makes a difference in running Insert's and/or doing Fetch's. Our DB was 11GB in size, I ran a dbcc shrinkdatabase and it shrank it to 5.5 GB in size, now that it is smaller will it run a select query faster as opposed to when we run large inserts and it has to automatically grow to accommodate the insert. I am trying to figure out if I should leave my .mdf file large or keep it small or does it even make a difference. I am only doing large inserts while loading data to get ready for production after that the inserts will be hourly but much smaller, however our queries to the DB after it is in production will be much more intensive.
Hope this makes sense. Appreciate your help.
View 2 Replies
View Related
Jan 9, 2006
Hi All,
I am writing a script that runs the optimization process. I am not sure how to test it? Any help is greatly appreciated.
View 9 Replies
View Related
Dec 20, 2006
Hello,
Is there a tool to optimize SQL procedures code?
Thanks,
Miguel
View 4 Replies
View Related
Jul 20, 2005
We're building a company wide network monitoring systemin Java, and need some advice on the database design andtuning.The application will need to concurrently INSERT,DELETE, and SELECT from our EVENT table as efficiently aspossible. We plan to implement an INSERT thread, a DELETEthread, and a SELECT thread within our Java program.The EVENT table will have several hundred million recordsin it at any given time. We will prune, using DELETE, aboutevery five seconds to keep the active record set down toa user controlled size. And one of the three queries willbe executed about every twenty seconds. Finally, we'llINSERT as fast as we can in the INSERT thread.Being new to MSSQL, we need advice on1) Server Tuning - Memory allocations, etc.2) Table Tuning - Field types3) Index Tuning - Are the indexes right4) Query Tuning - Hints, etc.5) Process Tuning - Better ways to INSERT and DELETE, etc.Thanks, in advance, for any suggestions you can make :-)The table is// CREATE TABLE EVENT (// ID INT PRIMARY KEY NOT NULL,// IPSOURCE INT NOT NULL,// IPDEST INT NOT NULL,// UNIXTIME BIGINT NOT NULL,// TYPE TINYINT NOT NULL,// DEVICEID SMALLINT NOT NULL,// PROTOCOL TINYINT NOT NULL// )//// CREATE INDEX INDEX_SRC_DEST_TYPE// ON EVENT (// IPSOURCE,IPDEST,TYPE// )The SELECTS areprivate static String QueryString1 ="SELECT ID,IPSOURCE,IPDEST,TYPE "+"FROM EVENT "+"WHERE ID >= ? "+" AND ID <= ?";private static String QueryString2 ="SELECT COUNT(*),IPSOURCE "+"FROM EVENT "+"GROUP BY IPSOURCE "+"ORDER BY 1 DESC";private static String QueryString3 ="SELECT COUNT(*),IPDEST "+"FROM EVENT "+"WHERE IPSOURCE = ? "+" AND TYPE = ? "+"GROUP BY IPDEST "+"ORDER BY 1 DESC";The DELETE isprivate static String DeleteIDString ="DELETE FROM EVENT "+"WHERE ID < ?";
View 2 Replies
View Related
Jul 17, 2007
There are two main tables in my app,in order to optimize search via scope condition, I set many indexs for these two tables
however,at the same time the two tables are also used for my etl app,everyday there are more than thousands of data need to be updated or inserted, but index is not suitable for huge modification,any idea about how to handle this?
thanks in advance
View 4 Replies
View Related
Oct 15, 2007
Hello,
what is the meaning about <MissingIndexGroup Impact="99.9521"> in the Queryplan? Should I create a Grouped Index? An what is the meaning about Impact="99.9521"?
If the Impact =100 you get a 100% better performance, and if the impact =20 ypu get a 20% better performance, is this the meaning?
finchen
View 4 Replies
View Related
Sep 30, 2015
I am using Full Text Index to index emails stored in BLOB column in a table. Index process parses stored emails, and, if there is one or more files attached to the email these documents get indexed too. In result when I'm querying the full text index for a word or phrase I am getting reference to the email containing the word of phrase if interest if the word was used in the email body OR if it was used in any document attached to the email.
How to distinguish in a Full Text query that the result came from an embedded document rather than from "main" document? Or if that's not possible how to disable indexing of embedded documents?
My goal is either to give a user an option if he or she wants to search emails (email bodies only) OR emails AND documents attached to them, or at least clearly indicate in the returned result the real source where the word or phrase has been found.
View 0 Replies
View Related
