2005 Query Help

May 1, 2006

Please help.

On sql 2000 i have a query like this where the columns are primary keys.

Select count(*) from db.dbo.table1

where convert(varchar(3), col1) + convert(varchar(10), col2) not in

(select convert(varchar(3), col1) + convert(varchar(10), col2) from db.dbo.table2)

It completes in 1 second with sql 2000. I have restored the db to sql 2005 and run the same query. The processors peg and it goes to la la land. I have updated statistics and installed SP1. Anyone have ideas? The sql 2005 is even way better than the sql 2000 box. If you have a better way to perform the same task, please let me know.

Thanks!

View 8 Replies

SQL 2005 V9.0.2047 (SP1) - The Query Processor Could Not Produce A Query Plan

May 15, 2006

Hi Everyone:

*Before* I actually call up Microsoft SQL Customer Support Services and ask them, I wanted to ping other people to see if you have ever ran into this exact error

"Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."

I would have searched the forums myself, but at this moment in time, search is broken :(

If anyone has run into this error before, what conditions would exist that this could happen? That is, if I can sniff this out with suggestions from the community, I would be happy to do so.

It is an oddity because if I alter a couple subqueries in the where clause [ i.e., where tab.Col = (select val from tab2 where id='122') ]to not have subqueries [hand coded values], then the t-sql result is fine. It's not as if subqueries are oddities... I've used them when appropriate.

fwiw - Not a newbie t-sql guy. ISV working almost daily with t-sql since MS SQL 2000. I have never seen this message before...at least I don't recall ever seeing it.

Thanks in advance for other suggested examination paths.

View 10 Replies View Related

SQL 2005 Query

Oct 29, 2007

what does '..' mean in the from clause of a SQL statement
example:

select IndexId, Name, EntityId, IsClustered, IsPrimaryKey, SqlFillFactor

from table_METABASE..EntityIndex

where isprimarykey = 1

order by EntityId, IsClustered desc

I can't find any info within the local help files.Thanks
-Chris

View 4 Replies View Related

Query Optimization In Sql 2005

Mar 28, 2008

Hi , How to optimize sql query in sql server 2005, any idea

View 4 Replies View Related

How Can I Do A Pivot Query SQL 2005?

Mar 30, 2006

Hi!
I have a table Items(id, name, section, id_format, price) and a table Format(id, format,order)
What I want to do is display: Name, Format1, Format2, Format3.
How can I acheive that result??
THanks!!
-JTM

View 2 Replies View Related

MsSQL 2005 Query Help

May 30, 2008

Code:

Select id from tbl_account in Game_User DB

id is binary data

USE Billing
INSERT INTO tblUser (userId,cpId,userTypeId,userStatusId,gameServiceId) VALUES ('test','1','1','9','6') where userId = id from tbl_account

and

INSERT INTO tblUserInfo (userNumber,userId,cpId,userTypeId,userStatusId,gameServiceId) VALUES ('123','test','1','1','9','6') where userNumber = userNumber and userId = userId from tblUser

Im new when it comes to making SQL queries, so i need help badly.

Basically what i need the query to do is call from a DB "User" id where the id is in Binary data. from that i need it to insert into 2 other tables in the Billing DB.

the UserId needs to be the same in all areas, and when it inserts into tblUserInfo it has to pull from userNumber from tblUser after the rows are entered so that the userNumber in tblUser and tblUserInfo are the same.

Hopefully you can help. if any further information is needed please just ask and ill try to give as much as i know.

Thanks

David

View 2 Replies View Related

Query Analyser In 2005 ?

Feb 14, 2006

I cannot find the query analyser in SQL 2005
do you know where it is ?

thank you

View 2 Replies View Related

SQL 2005 Query Performance

Sep 11, 2006

Hi, I want to know if anyone have any clue about the reason why this happens.
I have a table on SQL Server 7 with 320 thousand registers and when I execute a SELECT * on it, it takes about 6 seconds to give an answer. But the same table on SQL Server 2005 Ent takes about 16 seconds, Is it normal?:shocked: :shocked:

View 3 Replies View Related

Query Optimizer For Sql 2005

Feb 5, 2008

One quick question.

Is query optimizer a tool in sql that optimizes your queries? If so how do you use it?

Thanks

View 1 Replies View Related

Sql Server 2005 Query Help.

Jun 4, 2008

Hi,

I'm having a problem with the below query. I'm trying to show the total amount of placements(sales) group by each month. I also want to show how much of the placements(sales) have been invoiced again groped by each month. The problem I'm having is some placements(sales) have more two invoices linked to them so the placementfee for that placement is being doubled.

How do I write the below code so that it only sums placementfee based on a distinct placementid?

is it possible to but something like this in the where clause -
where p.placementid = distinct/unique

Thanks in advance

Dave,

select
DATENAME(MONTH, p.createdon) AS theMonth,
sum(p.placementfee) as 'Amount Boarded',
count(distinct p.placementid) 'Number of Deals'
,SUM(i.netsum) as 'Amount Invoiced',
SUM(p.placementfee)- SUM(i.netsum)as 'Waiting to invoice'

FROM placements p
left outer join placementinvoices pp on pp.placementid = p.placementid
left join invoices i on i.invoiceid = pp.invoiceid

WHEREp.createdon >= '20080101'
AND p.createdon < '20150101'

GROUP BY DATENAME(MONTH, p.createdon),
DATEPART(MONTH, p.createdon)

ORDER BY DATEPART(MONTH, p.createdon)

View 4 Replies View Related

Query Analyser In 2005 ?

Apr 18, 2007

Hi:
I am a Sql server 8.0 user,and using query analyser extensively. Is the query analyser available in SQL sever 2005 version ?
thank you in advance.

View 5 Replies View Related

Dynamic Query Help SQL 2005

Feb 13, 2006

I'm having a problem in getting a dynamic query in a sp working and thecode is below so can someone please help me? It would be very muchappreciated.ThanksGirogio--------------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[Search_Profile]@Country NVARCHAR(100) = null,@County NVARCHAR(100) = null,@Town NVARCHAR(100) = null,@AType bit,@PageIndex int,@NumRows int,@UsersCount int OutputASBEGINDECLARE @where_clause NVARCHAR(500);IF @Country IS NOT NULLBEGINSET @where_clause = @where_clause + ' AND aCountry = "' + @Country +'"'ENDIF @County IS NOT NULLBEGINSET @where_clause = @where_clause + ' AND aCounty = "' + @County + '"'ENDIF @Town IS NOT NULLBEGINSET @where_clause = @where_clause + ' AND aTown = "' + @Town + '"'ENDIF @AType IS NOT NULLBEGINSET @where_clause = @where_clause + ' AND Independent = "' +Convert(NVARCHAR, @AType) + '"'ENDDECLARE @Query1 NVARCHAR(1000);SET @Query1 = 'SELECT @UsersCount=(SELECT COUNT(*) FROM CustomProfileWHERE aActive = 1 ' + @where_clauseexec(@Query1)DECLARE @startRowIndex int;SET @startRowIndex = (@PageIndex * @NumRows) +1;DECLARE @Query2 NVARCHAR(1000);SET @Query2 = 'WITH UsersProfiles as (SELECT ROW_NUMBER() OVER (ORDER BY t.apubName) as Row, t.UserId,t.apubName, t.aCounty, t.aTown, u.UserNameFROM CustomProfile t, vw_aspnet_Users uWHERE t.UserID = u.UserID AND aActive = 1 ' + @where_clauseDeclare @Query3 NVARCHAR(1000)SET @Query3 = 'SELECT UserId, apubName, aCounty, aTown, UserNameFROM ' + @Query2 +' WHERE Row BETWEEN ' + Convert(NVARCHAR, @startRowIndex) + ' AND ' +Convert(NVARCHAR, @startRowIndex+@NumRows-1)exec(@Query3)END

View 2 Replies View Related

Run Multiple Query On VB 2005

Apr 3, 2007

Assume that I got 10 line of insert query to be carry out, how can I run all these 10 insert query at on time instead of using sqlcommand.executenonquery for 10 time. Thanks.

View 8 Replies View Related

SQL 2005 Query Designer

Oct 17, 2007

Hi,

My database has just recentrly been upgraded to SQL 2005 and i have noticed a change in the interface when writing a query in "Query Designer"

For one of our tables we have a 'Time' field. I know that time only doesnt exist yet and upon insertion a default date of 30/12/1899 is automatically inserted. Im happy with that!

My concern is with Query Designer. If i build a query using the criteria grid and for the time field i filer on >=13:00:00 (example), the SQL that it creates looks like this:

(TIME >= CONVERT(DATETIME, '2007-10-17 13:00:00', 102))

As you can see is automatically inserts the current date instead of 30/12/1899. This obviously causes my sql to not work unless i manually change the date in the SQL to 1899-12-30

In SQL 2000 i did not have to do this, it would automatically write the SQL as :

(TIME >= CONVERT(DATETIME, '1899-12-30 13:00:00', 102))

Even if i enter 30/12/1899 13:00:00 into the criteria grid it still converts to current date.

Is there a setting somewhere for the Query Designer so that it defaults to 30/12/1899??

Regards,

Eddie S

View 2 Replies View Related

Plz Help To Solve This Sql Query (MS Sql 2005)

Mar 26, 2008

my tables as follows and data as follows

AB_Corporate_Project
Fields are : ab_crp_id , cust_name , owner_rep_id

Data in table AB_Corporate_Project is as follows

ab_crp_id cust_name owner_rep_id
1 harry 3
2 msas 2

AB_Plant_Project
Fields are : ab_plant_id , ab_name , owner_rep_id

Data in Table AB_Plant_Project

ab_plant_id ab_name owner_rep_id
1 abc 1
2 def 2

Other_Project
Fields are : other_proj_id,ot_name, owner_rep_id

Data in table Other_Project

other_proj_id ot_name owner_rep_id
1 xyz 2
2 cdf 3

Owner_Rep

Fields are : owner_rep_id,owner_name

Data in Table Owner_Rep

owner_rep_id owner_name
1 henry
2 hologa
3 tmw

Daily_Time_Entry
Fields are: dl_id,dt_id,project_type,project_id,time_st
Data in table Daily_Time_Entry

dl_id dt_id project_type project_id time_st
1 03/23/08 AB Corporate 1 1.20
2 03/23/08 AB Corporate 2 3.25
3 03/21/08 AB Corporate 1 2.25
4 03/23/08 AB Plant 1 4.35
5 03/23/08 AB Plant 2 4.50
6 03/23/08 Other Project 1 3.24
7 03/23/08 Other Project 2 4.35

I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as follows

dl_id dt_id project_type project_id time_st owner_rep_id owner_name
1 03/23/08 AB Corporate 1 1.20 3 tmw
2 03/23/08 AB Corporate 2 3.25 2 hologa
3 03/21/08 AB Corporate 1 2.25 3 tmw
4 03/23/08 AB Plant 1 4.35 1 henry
5 03/23/08 AB Plant 2 4.50 2 hologa
6 03/23/08 Other Project 1 3.24 2 hologa
7 03/23/08 Other Project 2 4.35 3 tmw

Also in project_type AB Corporate for AB_Corporate_Project, AB Plant for AB_Plant_Project , Other Project for Other_Project

I write query for this is as follows

Select dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac. owner_rep_id,ab. owner_rep_id,ow. owner_rep_id,ow. owner_name
From Daily_Time_Entry dl left outer join
AB_Corporate_Project ac on dl. project_id = ac. ab_crp_id and dl. project_type=�� AB Corporate�� left outer join AB_Plant_Project ab on dl. project_id =ab. ab_plant_id and
dl. project_type=�� AB Plant�� left outer join Other_Project op on dl. project_id = op. other_proj_id and dl. project_type=�� Other Project�� inner join Owner_Rep ow on
(ow. owner_rep_id = ac. owner_rep_id) or(ow. owner_rep_id = ab. owner_rep_id)or
(ow. owner_rep_id = op.owner_rep_id)

So how can I write query to show output as follows or any other way or how to create Sql cursors for to show output
Plz help to solve this query to show output as specified.

View 3 Replies View Related

Need Help On MDX Query - SSRS 2005

Mar 25, 2008

Hello Everyone,

I have been given a task to develop a SSRS report that Mimic's a ASP.net grid. this grid has four column headers such has

Measures
Jan 2006
Feb 2006
Mar 2006
First "Measure" Column as all the measure captions as the data in rows and other 3 columns (Jan 2006,Feb 2006,Mar 2006 Dimensions) has the measure values as the data in the row.

Measure Jan 2006 Feb 2006 Mar 2006
Profit 12.32 15.23 15.0

Revenue 12.33 16.33 11.22

ROI 2 4 5

In SSRS MDX is not flexible as writing in Query Analyzer , can some one give me the idea how to create this in SSRS report designer.

Thanks,

Pradeep.

View 4 Replies View Related

How To Get Node Name In SQL 2005 Nodes Query? Thanks

Nov 30, 2006

for example,xml like
'<name>test</name><age>10</age>'
select ? from @xml.nodes('/*')
how to get the node tag name like name and age?
thanks

View 4 Replies View Related

How To Get Row Numbers From SQL Express 2005 Query?

Nov 11, 2007

Hi, I'm using SQL Express 2005 and VIsual Studio 2005, and this sounds like it should be easy. I'm trying to return the row numbers of my queries, but if I use the Row_Number() command I get the following error: "The OVER SQL construct or statement is not supported." So, is Row_Number() not supported in SQL Express 2005? If not, how can I return row numbers with my queries? Or, more specifically, how can I return a limited result set from a query (i.e. Return only row number 10-20)? My current command is as follows: SELECT (SELECT Row_Number() OVER (ORDER BY UserName) As RowNumber), *FROM UsersORDER BY UserName Thanks for any advice you can offer!

View 8 Replies View Related

Sql Server 2005 Query Questions

Apr 4, 2006

I recently upgraded to sql server 2005 for developing on my local system and cant seem to find the option that automatically sets the drop procedure at the top and the usernames on the bottom of a procedure that I script as new. I used to do it in the old query analyzer so Im sure its in there somewhere. Thanks in advance for any help.RyanOC

View 4 Replies View Related

Retrieving The First N Records From A SQL Query In VS 2005

Apr 6, 2006

Hi,
first off, I'm a TOTAL novice at this stuff, I'm just currently blundering my way through a complex site to learn stuff.
I'm trying to call the newest addition to a SQL database into a webpage, in this case, it'll be 'newest user', one result only. I've done several other data retrival sections using a datatable, but the guy who was helping me though it is unavailable at the moment and I get the feeling I've jumped into the deepend slightly.
Could anyone give me an example of how retrieving the First N Records from SQL should look in VS? Does it need to be in a data table or can it go in a label?
Sorry if this is somewhat vague, but as I said, I've really only been using VS for a week!

View 3 Replies View Related

Complex Sql Server 2005 Query

Nov 8, 2007

Hi,
A sql server table is populated with records every 2 minutes. See below sample table
In the table, the Import_Date is a datetime field.

create table tblData
(
ID int identity(1, 1),
SourceID int,
SourceCode varchar(255)
Security varchar(255),
Bprice decimal(12, 8),
Aprice decimal(12, 8),
ImportDate datetime
)

Here is a populated table.
I have left gaps for better visual checks for you.

IDSourceIDSourceCodeSecurityBpriceBpriceSizeApriceApriceSizeImportDate

11sourceASecA100.2299.1212007-11-07 16:24:31.297
22sourceWSecH95.789.432007-11-07 16:24:31.297
33SourceXSecS50.56176.4442007-11-07 16:24:31.297
44SourceQSecZ87.982007-11-07 16:24:31.297
55SourceJSecH100.299.1222007-11-07 16:24:31.297
66SourceKSecU2007-11-07 16:24:31.297
77SourceTSecA50.56387.112007-11-07 16:24:31.297

81sourceASecA100.2699.1222007-11-07 16:26:15.123
92sourceWSecH99.54489.432007-11-07 16:26:15.123
103SourceXSecS50.56219.332007-11-07 16:26:15.123
114SourceQSecZ16.9887.982007-11-07 16:26:15.123
125SourceJSecH100.2199.1222007-11-07 16:26:15.123
136SourceKSecU2007-11-07 16:26:15.123
147SourceTSecA50.56287.1112007-11-07 16:26:15.123

151sourceASecA100.2187.1112007-11-07 16:26:15.123
162sourceWSecH99.6689.4322007-11-07 16:26:15.123
173SourceXSecS50.56219.332007-11-07 16:26:15.123
184SourceQSecZ16.98387.9832007-11-07 16:26:15.123
195SourceJSecH100.2399.1232007-11-07 16:26:15.123
206SourceKSecU2007-11-07 16:26:15.123
217SourceTSecA101.32587.1132007-11-07 16:26:15.123
...

I am trying to build a sql query to show which source is offering the max(Bprice) and who is offering the min(Aprice).
In addition if more than one sources are offering the same prices then they should be shown as shown below in the first record i.e. (SourceA, SourceT) --> 3 + 1 = 4
This is what I would like to see:

SecurityMax_BpriceBprice_SizeBprice_SourceCodeMin_ApriceAprice_SizeAprice_SourceCode

SecA101.325SourceT87.114SourceA, SourceT
SecH100.23SourceJ89.432SourceW
SecS50.562SourceX19.33SourceX
SecZ16.983SourceQ87.983SourceQ

What is the sql query to do this please?

This is what I have started with but it is not correct...

select
Security,
max(Bprice) as 'Max_Bprice',
SourceCode as 'Bprice_SourceCode',
min(Aprice) as 'Min_Aprice',
SourceCode as 'Aprice_SourceCode'
from
tblData
group by
Security,
SourceCode

View 6 Replies View Related

How To Scheduling A Query In Sql Server 2005

Apr 16, 2008

I want to schedule a query that ll insert a record in a table of the database at every 02:00 am. How should i do that. i am uisng sql server 2005?

Muhammad Saifullah

View 7 Replies View Related

Query Designer In SQL Server 2005

May 9, 2007

I would like to be able to use the Query Designer without having to close it out to test the query.

I seem to recall using Query Designer in 2000 as a stand-alone app outside of Enterprise Manager.

How can I use Query Designer AND have the results show up in the same window?

View 2 Replies View Related

Do You Miss Query Analyzer In SQL 2005?

May 15, 2007

I tried. I really did to adapt to using Management studio as a replacement, but I just can't make the jump. I went ahead and installed SQL 2000 QA and have gone back to using it for ad-hoc query support and other routine SQL tasks. For me the main thing is the "Notepad-esque" lightness of QA compared to the heavier SQL tools that allows me to quickly open a SQL script file and get to work on it quickly. I appreciate an integrated tool, but I would like to see future versions allow you to use it either as a standalone or within integrated platform. Am I alone in this? Anyone else feeling this pain?

View 6 Replies View Related

SQL Server 2005 - SELECT Query

Oct 18, 2006

HiI currently have a select query with "Description = 'input from userhere'" which basically returns the associated row containing theDescription field exactly as typed by the user, however, would it bepossible to, if not found, return the closest match? I am usingMicrosoft Visual Studio 2005 with C# as language and Microsoft SQLServer 2005 as database.Regards,Lionel Pinkhard---avast! Antivirus: Outbound message clean.Virus Database (VPS): 0642-0, 2006/10/17Tested on: 2006/10/18 12:46:07 PMavast! - copyright (c) 1988-2006 ALWIL Software.http://www.avast.com

View 4 Replies View Related

Query RowVersion In SQL Server 2005

Feb 6, 2007

Hello all,I am new at SQL Server 2005 and have been reading everything I canfindabout the new optimistic concurrency control mechanisms. As far as Ican tell, the Snapshot Isolation Level is based avoids the use ofshared locks using rowversioning instead.To control rowversions in SQL Server 2000 I was using an extra columnin each table containing a rowversion datatype. What i do with this isfind out if a row was modified. Is it possible in SQL Server 2005 toavoid the use of this extra column, using the new versioning features?What I mean is, if the Snapshot Isolation already manages rowversionsin TempDB, is it possible for me to query those versions using somestored procedures or functions given by SQL Server 2005 system? Whatfunctionalities does SQL Server 2005 gives me for that (anydocumentation pointer would help)?This would allow me to do the exact same processing that i was doingwith the rowversion column in SQL Server 2000 without the need forthat extra column.For all of you my thanks and best wishesSusana Guedes

View 1 Replies View Related

SSAS 2005 SQL Query Urgently Help !!

Aug 13, 2007

Hi, I'm building a SSAS Integration project, it consist of extracting data from a Microsoft Access 2003 database and consolidate data into a SQL Server 2005 database for datawarehousing.
I need to make several operations for integration, but i'm struck in the following:

- I added an Ole DB Origin that connects to my access database
- I made an sql query with multiple joins that extract all data i need for first instance.
- in data i extracted, i got one column named: FECHA (and many other colums), it contains data of the date of the actual sale; but in previous steps i fill a table named DimTiempo from my sql server database that contanins all the dates (with no repeating) of the sales and added an unique primary key for them; so, now i need to get that primary key for each column FECHA i gotted in my sql query in OLEDB ORIGIN, for example:

I have: 20/20/2004 (column FECHA on my OLEDB ORIGIN gotted with an sql query with multiple joins)
I need: 001 (extracted from DimTiempo, its the primary key of the registry 001 - 20/20/2004)

I need that code or primary key to insert into my new table on my sql server database because its related directly with my DimTiempo table filled previously.

For more directions, i don't have that primary key column on the original access data base; i reestructure completely the database on sql server to make the datawarehouse work fine.

Please, i hope you help me, tell me what do i need to do to get all the data to fill my sql server 2005 database.

Thanks.

View 4 Replies View Related

Query Performance - SQL Server 2005

Oct 17, 2007

Hi,

I am having a table with 40 columns and it contains 4 million records. I got the results for one year in 40 secs. After tuning, it is retuning in 24 secs( what i have done is i created index on order by fields).

Can you please suggest me in which way I can increase the performance.

Note: I am using only one table with Primary Key.

Thanks
Dinesh

View 7 Replies View Related

SQL 2000 Vs SQL 2005 - Must Do - Syntax And Query Changes

Feb 1, 2008

Hi,

We are into a phase of retiring SQL 2000 database and replacing with SQL 2005. Could you please guide me to get a list of all the MUST DO changes on syntax, statements/query those have been changed in 2005 when compared to 2000.

I know there is a list of new features list on site, but it doesn't tells me precisely what all syntaxes will result differently then expected in 2000. For Example - if we open help for SET ANSI_NULLS ON, we can read that MS is suggesting to avoid its use as it will be absolete in later version. So do we have a list of all such things in one place that we can read and analyse our code for changes to be done now instead of finding it later.

Thanks for your time on this one, in advance.

Regards
Pankaj

View 3 Replies View Related

Dynamic Column In The Query Using SQL 2005

Aug 11, 2006

Hi All,

I am using Micosoft Visual Studio Report Desinger. with MS SQL 2005.

I have a table transac table fields are likely,

location,date,amount values,

USA,01/07/2006,3000

SG,01/07/2006,2500

USA,02/07/2006,6000

SG,02/07/2006,3500

USA,03/07/2006,1000

SG,03/07/2006,6700

USA,04/07/2006,500

SG,04/07/2006,200

Am writing query for date = 04/07/2006

select location,date,amount from transac where date = 04/07/2006

I wanted to add two more column in the query which is

a.two days before what is the amount

b. From 01/07/2006 to 04/07/2006 what is the amount

The result I want to be

Location,date,amount,2daysbefore,uptodate

USA,04/07/2006,500,6000,10500

SG,04/07/2006,200,3500,12900

How to write a query ?.

I am writing this query from DataSet for Report Desinger.

Is there any way to include this two column.

Please Advise,

Regrads Saleem

View 1 Replies View Related

No Available Databases In A New Query In SSMS 2005

Mar 7, 2007

When I create a new query window in SSMS 2005 (SP2) , I do not have any available database in de SQL Editor pane. Though in the Object Explorer window below, I can access all objects of all databases. The only way to query a table is by using the 'Open Table' feature in the Object Explorer window, then right-click on Pane-->SQL .

I also can open a stored procedure, but not modify it as the SQL Editor pane is disabled.

The SQL-Editor pane is available for all the other servertypes (Analysis, Reporting, Integration).

All services all started.

I re-installed completely SQL2005 and SP2 : nothing changed.

What am I missing ???

Versions = Microsoft SQL Server Management Studio 9.00.3042.00

SQL Developer Edition 2005 (Service Pack 2)

Windows XP Pro 5.1 (build 2600 Service Pack 2)

Any help is welcome !!!

View 6 Replies View Related

Query Plan In SQL Server 2005 SP2

Aug 14, 2007

Hello,

The query included at the end of this post seems to use the 'wrong' index when executing and takes 2+ minutes to run. When I provide an index hint, it runs in under 1 second. This happens in both the Production and Development environments; both servers run SQL Server 2005 SP2.

When I run both the queries together in Query Analyzer (or SSMS), the Query Cost (relative to the batch) value for the query with the index hint = 83.24%.

Profiler stats for Original Query:

Duration = 130484 ms
CPU = 111141 ms
Reads = 85470

Profiler stats for Index Hint Query:

Duration = 64 ms
CPU = 687 ms
Reads = 5558

Statistics are updated every night (w/Fullscan)
Indexes are rebuilt/defragmented daily based on fragmentation levels.

What am I missing here/how do I fix this performance issue?
Doesn't Profiler take into account the "Subtree Cost" for the Index Seek (which Query Analyzer & SSMS consider to be so expensive)? What Events/Columns do I need to include in the Profiler trace to see this statistic when the query is executing?

Thanks much,
Smitha
------------------------------------------------------------------------

QUERY:
select min(AccessLevel)
from Groups-- WITH(INDEX = ByClassID)
where name = 'Student Leader'
and classid in (2067,2063,2069,2070,2079,2072,2073,2074,2075,2076,2077,2073,2079,2030,2039,2032,201,2034,2035,2036,2037,201,2039,2090,
202,2092,2093,2094,2095,2096,2097,2093,202,24,2909,2902,2903,2904,2905,25,2907,2903,2909,220,229,222,223,224,225,226,227,223,229,2920,2929,2922,2923,2924,22,2926,2927,2923,2929,2930,26,2932,291,2934,2935,28,2937,291,
26,2940,2949,211,2943,2944,2945,2946,2947,2943,2949,27,2959,2952,2953,2954,2955,2956,2957,2953,2959,2960,2969,2962,2963,2964,2965,2966,2967,2963,2969,2970,2979,2972,2973,2974,2975,2976,2977,2973,2979,2930,26,2932,291,2934,2935,28,
2937,291,26,220,229,222,223,224,225,226,227,223,229,2200,2209,2202,2203,2204,2205,34736,34739,34749,34742,34743,34744,34745,34746,34747,34743,34749,3471,34759,34392,34393,34396,34397,34962,34937,3491,346,3420,3429,3423,3424,35360,35695,35696,35709,
1056,1057,1223,4107,1256,1257,1259,1269,1262,1263,1265,1267,1263,1269,1270,1272,1273,1274,1275,1276,1277,1273,1279,1230,1239,1232,121,1234,1235,1236,1237,121,1290,122,1292,1293,1294,1295,1296,1293,122,1300,1309,1302,1303,1304,1305,1306,
1307,1494,1459,1469,1462,1466,1467,1469,1470,1472,1474,1475,1477,1432,141,1435,1436,1437,1439,1494,1496,1497,142,113,115,117,113,119,1590,152,1592,1593,1594,1595,1596,1593,152,1520,1529,1522,1523,1524,1525,1527,1539,151,1534,1536,
151,1540,1543,1544,1545,1547,1572,1573,1574,1576,1577,1573,1579,1539,53969,64763,67735,67736,63967,1529,1530,1532,151,1534,1535,1536,1537,151,1539,1540,1549,1542,1543,1545,1546,1547,1543,1549,151,1559,1553,1555,1557,1553,1559,1560,1569,1562,
1563,1564,1565,1566,1567,1563,1569,1570,1579,1572,1573,1574,1575,1576,1577,1573,1579,1530,1539,1532,151,1534,1535,1536,152,1609,1602,1603,1604,1605,1606,1607,1603,1609,1690,162,1692,1693,1694,1695,1696,1697,1693,162,1629,1622,1635,1637,
1640,1642,1646,1649,1652,1657,1669,1664,1667,1679,1634,1637,1693,1703,1706,172,1795,1793,1722,1726,1764,1392,1393,1394,1395,1396,132,1320,1329,1322,1323,3629,3622,3623,3624,3625,3626,3627,3623,3629,36200,36209,36202,36203,36204,36205,36206,36207,
36203,36209,36290,37093,37094,37095,37096,37097,37093,3702,37020,37029,37022,37023,37024,37025,37026,37027,37023,37029,37030,37039,37032,3701,37034,37035,37036,37037,3701,37039,37040,37049,37042,37043,37044,37045,37046,37047,37043,37049,3701,37059,37052,37053,37054,37055,37056,37057,
37053,37059,37060,37069,37062,37063,37064,37065,37066,37067,37063,37069,37070,37079,37072,37073,37074,37075,37076,37077,37073,37079,37030,37039,37032,3701,37034,37035,37036,37037,3701,37039,37090,3702,37092,37093,37094,37095,37096,37097,37093,3702,374,37909,37902,37903,37904,37905,
375,37907,37903,37909,3720,3729,3722,3723,3724,37320,37329,37322,37323,37324,37325,37326,37327,37329,3710,3719,3712,3713,3714,3715,3716,3717,3713,37340,37349,37342,37343,37344,37345,37346,37347,37343,37349,3731,37359,37352,37353,37354,37356,37357,37353,37359,37360,37369,
37362,37363,37364,37365,37366,37367,37363,37369,3715,3716,3717,3713,3719,37340,37349,37342,37343,37344,37345,37346,37347,37343,37349,3731,37359,37352,37353,37354,37355,37356,37357,37353,37359,37360,37369,37362,37363,37364,37365,37366,37367,37363,37369,37370,37379,37372,37373,37374,
37375,37376,37377,37373,37379,3710,3719,3712,3713,3714,3715,3716,3717,3713,3719,37390,3732,37392,37393,37394,37395,37396,37397,37393,3732,374,37909,37902,37903,37904,37905,375,37907,37903,37909,3720,3729,3722,3723,3724,3725,3726,3727,3723,3729,37920,37929,37922,
37923,37924,372,37926,37927,37923,37929,37930,376,37932,3791,37934,37935,378,37937,3791,376,37940,37949,3711,37943,37944,37945,37946,37947,37943,37949,377,37959,37952,37953,37954,37955,37956,37957,37953,37959,37960,37969,37962,37963,37964,37965,37966,37967,37963,37969,37970,
37979,37972,37973,37974,37975,37976,37977,37973,37979,37930,376,37932,3791,37934,37935,378,37937,3791,376,3720,3729,3722,3723,3724,3725,3726,3727,3723,3729,1000,1009,1002,1003,1004,1005,1006,1007,1003,1009,1090,102,1092,1093,1094,1095,1096,1097,1093,
102,1020,1029,1022,1023,1024,1025,1026,1027,1023,1029,1030,1039,1032,101,1034,1035,1036,1037,101,1039,1040,1049,1042,1043,1044,1045,1046,1047,1043,1049,101,1059,1052,1053,1054,1055,1056,1057,1053,191,1934,1935,18,1937,191,16,1940,
1949,111,1943,1944,1945,1946,1947,1943,1949,17,1959,1952,1953,1954,1955,1956,1957,1953,1959,1960,1969,1962,1963,1964,1965,1966,1967,1963,1969,1970,1979,1972,1973,1974,1975,1976,1977,1973,1979,1930,16,1932,191,1934,1935,18,1937,191,
16,120,129,122,123,124,125,126,127,123,129,1200,1209,1202,1203,1204,1205,1206,1207,1203,1209,1290,122,1292,1293,1294,1295,1296,1297,1293,122,1220,1229,1222,1223,1224,1225,1226,1227,1223,1229,1230,1239,1232,121,1234,1235,1236,
1237,121,1239,1240,1249,1242,1243,1244,1245,1246,1247,1243,1249,121,1259,1252,1253,1254,1255,1256,1257,1253,1259,1260,1269,1262,1263,1264,1265,1266,1267,1263,1269,1270,1279,1272,1273,1274,1275,1276,1277,1273,1279,1230,1239,1232,121,1234,
1235,1236,1237,121,1239,1290,122,1292,1293,1294,1295,1296,1297,1293,122,1300,1309,1302,1303,1304,1305,1306,1307,1303,1309,1390,132,1392,1393,1394,1395,1396,1397,1393,132,1320,1329,1322,1323,1324,1325,1326,1327,1323,1329,110,119,112,
113,114,115,116,117,113,119,1340,1349,1342,1343,1344,1345,1346,1347,1343,1349,131,1359,1352,1353,1354,1355,1356,1357,1353,1359,1360,1369,1362,1363,1364,1365,1366,1367,1363,1369,1370,1379,1372,1373,1374,1375,1377,1373,1379,110,119,
112,113,115,116,117,113,119,132,1392,1393,1729,1723,9441,94497,94493,9442,9410,9419,9412,9413,9414,9415,9416,9417,9413,9419,94590,9452,94593,94594,94595,94596,94597,95576,97396,97909,93057,4640,4653,4654,4656,4657,4653,4659,4660,4669,4662,
4663,4664,4665,4666,4669,4670,4679,4672,4673,4675,4676,4677,4639,4632,461,4634,4635,4636,4637,4639,5290,522,5294,5295,5296,5370,90742,20953,20959,2239,2234,222,2292,2293,2294,2296,2297,222,29209,29204,29205,29206,
29207,29203,29209,29292,29294,29295,29297,29223,29225,29226,29223,29229,29230,29239,2921,29234,23903,23909,2320,24393,2521,25252,25253,25256,25253,25269,25262,25264,25266,25267,25269,25270,25279,25272,25273,25274,25276,25277,25273,25230,25239,25232,
25235,25237,2521,25239,25290,25292,25293,25294,2790,2793,272,2729,2749,2745,2746,271,2759,2753,2755,2753,2759,2760,2762,2764,2766,2769,2770,2779,2772,2777,93029,8747,8743,8749,871,8759,8752,8755,8757,8769,8764,8765,
8766,8769,8770,8779,8773,8774,8775,8776,8777,8773,8779,8730,8739,8732,871,8734,8739,8790,872,8792,8795,8797,8793,872,8300,8309,8303,8304,8306,8307,8309,8394,8396,8397,832,8322,8326,8323,810,819,812,813,
814,937952,6045,6097,93203,93206,619,617,632,6395,6400,6403,6406,6490,6497,6422,6425,6423,6432,6434,6435,6437,6457,6453,6459,6460,6463,6464,6465,6466,6469,6470,6472,6473,6475,6477,6473,6430,6432,6434,6435,6437,
641,6439,6490,642,6492,6493,6494,6495,6497,6493,642,9370,9379,9372,9373,9374,9375,9376,9377,9373,9379,6590,6592,6594,6593,6520,6523,6524,6527,6523,6529,6539,651,6534,6535,6536,6539,6540,6544,6545,6547,6549,
6559,6552,6553,6555,6556,6557,6553,6560,6569,6564,6565,6567,6563,6570,6579,6572,6573,6574,6575,6577,6573,6530,651,6534,651,6590,652,6592,6594,6596,6597,6593,652,6609,6632,661,6634,6635,6636,6637,661,6647,
6643,6659,6652,6653,6654,6655,6656,6657,6653,6659,6669,6663,6664,6665,6666,6667,6663,6670,6672,6673,6675,6676,6677,6630,6632,661,6636,661,6693,6694,6695,6696,6697,6693,6700,6702,6703,6704,11439,11440,11449,11442,
11443,11444,11669,11679,11676,11677,1161,11635,11637,11639,1162,11697,11693,11709,11709,11790,11723,11727,11732,11735,11737,11749,11744,11743,1171,11300,11306,11307,11309,1132,11977,94170,94179,94172,945454,945934,946464,946465,946466,94123,94123,94139,
94134,94135,94143,9411,94153,94155,94157,7239,7232,721,7692,7694,7696,7697,7693,7703,7704,7705,7329,7324,7326,714,716,719,7359,7352,7359,7363,7367,7379,719,712,713,714,715,716,717,713,719,7390,732,7392,
7393,7394,7395,7396,7397,7393,732,74,7909,7902,7903,7929,7924,7923,7937,7947,7959,7953,7955,7957,7959,7962,7963,7964,7966,7967,7963,7969,7932,791,7935,791,722,723,9549,9543,952600,952629,952627,952629,952657,952653,
952662,952690,952697,952797,95272,952720,952724,952729,954597,954964,955669,955790,95572,955792,95629,957069)

INDEX Definition:
CREATE INDEX ByClassID ON Groups(ClassID) INCLUDE(Name,AccessLevel)

View 10 Replies View Related

Sql Server 2005 Query Analyser

Nov 29, 2006

is there a query analyzer for sql server 2005 as in sql server 2000?

bcoz i downloaded the 180 days evaluation version & installed it .

i have worked with sql server 2000. but there is no query analyzer for 2005 how can i write queries & execute?

of course there is vs 2005 which has come with sql server 2005 evaluation copy.

plz help!

bcoz i really want to work with sql server 2005

thanks in advance for ur replies

View 8 Replies View Related