Does SQL2005 still require all FTI keywords to match in the same column?

Hello,
My understanding of full-text index searching is that CONTAINS()
requires that all the keywords supplied (separated by AND) must match
in the same column in order for the record to be considered a match and
returned. So if we have a situation where we need keywords to match in
multiple columns, we either need to use multiple CONTAINS() statements,
use FREETEXT() and allow it to manipulate our keywords, or create a
single column containing all the text we want indexed for a row and
search that unified column.
Is this correct? If so, is this shortcoming fixed in SQL2005?
Thanks,
Thomas
Hi Thomas,
I am afraid SQL 2005 shows the same behavior. FTS is designed to search for text/documents, which normally are stored in the same column. Can you please explain a bit more why you need to have queries that involve several word matches for several columns?
Thanks!
Fernando Azpeitia Lopez,
Program Manager
SQL Server FTS team
--Original Message--
From: Thomas
Posted At: Wednesday, February 15, 2006 9:33 AM
Posted To: microsoft.public.sqlserver.fulltext
Conversation: Does SQL2005 still require all FTI keywords to match in the same column?
Subject: Does SQL2005 still require all FTI keywords to match in the same column?
Hello,
My understanding of full-text index searching is that CONTAINS()
requires that all the keywords supplied (separated by AND) must match
in the same column in order for the record to be considered a match and
returned. So if we have a situation where we need keywords to match in
multiple columns, we either need to use multiple CONTAINS() statements,
use FREETEXT() and allow it to manipulate our keywords, or create a
single column containing all the text we want indexed for a row and
search that unified column.
Is this correct? If so, is this shortcoming fixed in SQL2005?
Thanks,
Thomas
|||Hi Fernando,
I'm not sure exactly how to respond... SQL Server excels at storing all
kinds of data. Our records are hybrids of large text fields, numbers,
dates, etc. We have multiple text columns. Just as one example, let's
say we have an email database where we have the headers stored in
different fields, at the very least, subject and body in different
fields. A person enters multiple keywords, and even if some are in the
subject and some are in the body, the record should match. That's just
a basic example.
We were really blown away when we found out this does not behave in
this fashion. It makes no sense, from our perspective as a user, to
query * (all columns) for some keywords, and require they all match in
only a single column.
Another problem we are fighting that lends itself to being allowed to
find the keywords across columns: tagging. We have found that we can
really speed up our searches if the entire search is performed on the
FTS side of the equation.
Contrived Example: You want to find all records that contain the word
"house" and were created in September 2005. Your table holds 100,000
records. Let's say 50,000 of those records contain the word "house."
But only 1000 were created in September 2005. You could do a search
like this:
SELECT * FROM ourtable WHERE CONTAINS(*, '"house"') AND createdate
BETWEEN '09/01/2005 00:00:00' AND '09/30/2005 23:59:59'
But we have found this search is really slow. The slowdown is in the
number of records being returned that contain "house" even though most
of them are not going to pass our SQL Server filter of the date. So we
want to create a tag column of textual things that we can search on the
FTS. Then our query would be:
SELECT * FROM ourtable WHERE CONTAINS(*, '"house" and "DT200509"')
Now we've shifted the date requirement over to the FTS side of the
search. Admittedly, a hack, but it should work. As you know, it
doesn't, because the two keywords will be found in two different
columns.
So what we are forced to do now is the ultimate hack: create a single,
new text field where we are duplicating all of our data from multiple
columns, adding our "tags" for constraining the searches, and then
full-text indexing this single column in order to get fast searches.
It seems weird that FTS is designed to search documents when SQL Server
is designed to hold all kinds of data. Shouldn't FTS be designed to
efficiently search what SQL Server can hold?
Thanks for your time.
Regards,
Thomas
|||Hi Thomas,
I see your problem. Let me think about the best solutions.
Most of FTS users are focus in get great functionality to efficiently search inside a document, rather than to search parts in different documents stored in different columns. Anyway, for these cases like yours, we support multiple CONTAINS. Is true that
the performance is not as good as with one single CONTAINS but if we would allow from the beginning to have several columns look ups in a single CONTAINS, we would probably finish with similar performance even if you are just writing one clause.
The good news is the following.
-For next FTS release we have several architecture improvements that will improve dramatically the joined queries. This means that mix relational (date for instance) with FTS search will be efficient. Following your example, before look the FTS side, the
optimizer will get the few ones that pass the date filter and then these ones will be FT searched.
This improvement also will improve multiple CONTAINS queries, so you will not longer experiment pain.
-For now, the best you can do is to use computed columns. These columns will contain virtually the same data than the original columns and you can create a FT index on that column. The indexing time will take longer as you are merging 2 or more columns bu
t at query time you will be able to query efficiently and find what you look for.
Does this help?
Regards,
Fernando Azpeitia Lopez,
Program Manager
SQL Server FTS team
--Original Message--
From: Thomas
Posted At: Wednesday, February 15, 2006 9:34 PM
Posted To: microsoft.public.sqlserver.fulltext
Conversation: Does SQL2005 still require all FTI keywords to match in the same column?
Subject: Re: Does SQL2005 still require all FTI keywords to match in the same column?
Hi Fernando,
I'm not sure exactly how to respond... SQL Server excels at storing all
kinds of data. Our records are hybrids of large text fields, numbers,
dates, etc. We have multiple text columns. Just as one example, let's
say we have an email database where we have the headers stored in
different fields, at the very least, subject and body in different
fields. A person enters multiple keywords, and even if some are in the
subject and some are in the body, the record should match. That's just
a basic example.
We were really blown away when we found out this does not behave in
this fashion. It makes no sense, from our perspective as a user, to
query * (all columns) for some keywords, and require they all match in
only a single column.
Another problem we are fighting that lends itself to being allowed to
find the keywords across columns: tagging. We have found that we can
really speed up our searches if the entire search is performed on the
FTS side of the equation.
Contrived Example: You want to find all records that contain the word
"house" and were created in September 2005. Your table holds 100,000
records. Let's say 50,000 of those records contain the word "house."
But only 1000 were created in September 2005. You could do a search
like this:
SELECT * FROM ourtable WHERE CONTAINS(*, '"house"') AND createdate
BETWEEN '09/01/2005 00:00:00' AND '09/30/2005 23:59:59'
But we have found this search is really slow. The slowdown is in the
number of records being returned that contain "house" even though most
of them are not going to pass our SQL Server filter of the date. So we
want to create a tag column of textual things that we can search on the
FTS. Then our query would be:
SELECT * FROM ourtable WHERE CONTAINS(*, '"house" and "DT200509"')
Now we've shifted the date requirement over to the FTS side of the
search. Admittedly, a hack, but it should work. As you know, it
doesn't, because the two keywords will be found in two different
columns.
So what we are forced to do now is the ultimate hack: create a single,
new text field where we are duplicating all of our data from multiple
columns, adding our "tags" for constraining the searches, and then
full-text indexing this single column in order to get fast searches.
It seems weird that FTS is designed to search documents when SQL Server
is designed to hold all kinds of data. Shouldn't FTS be designed to
efficiently search what SQL Server can hold?
Thanks for your time.
Regards,
Thomas
|||Fernando Azpeitia Lopez wrote:

> The good news is the following.
> -For next FTS release we have several architecture improvements that will improve
>dramatically the joined queries. This means that mix relational (date for instance) with
>FTS search will be efficient. Following your example, before look the FTS side, the
>optimizer will get the few ones that pass the date filter and then these ones will be FT
>searched. This improvement also will improve multiple CONTAINS queries, so you will
>not longer experiment pain.
When you say the "next FTS release," does this mean an upgrade to SQL
2005's FTS, or do you mean the FTS that is released in whatever version
comes after SQL Server 2005 (i.e. SQL Server 2010 ;-)

> -For now, the best you can do is to use computed columns. These columns will
>contain virtually the same data than the original columns and you can create a FT index
> on that column. The indexing time will take longer as you are merging 2 or more
>columns but at query time you will be able to query efficiently and find what you look
>for.
Can you give me an example of how to do a FTI on a computed column? We
are currently using SQL Server 2000, and only preparing our move to
2005, so are not yet familiar with 2005 completely.
Thanks,
Thomas
|||Hi Thomas,
When I say next FTS release I mean the next release, not any upgrade. And don’t worry, the next version should be no longer than 2007
In a following post I will let you know the steps to work with computed columns in SQL 2005.
Regards,
Fernando Azpeitia Lopez,
Program Manager
SQL Server FTS team
--Original Message--
From: Thomas [mailto:tomwinzig@.gmail.com]
Posted At: Friday, February 17, 2006 10:50 AM
Posted To: microsoft.public.sqlserver.fulltext
Conversation: Does SQL2005 still require all FTI keywords to match in the same column?
Subject: Re: Does SQL2005 still require all FTI keywords to match in the same column?
Fernando Azpeitia Lopez wrote:

> The good news is the following.
> -For next FTS release we have several architecture improvements that will improve
>dramatically the joined queries. This means that mix relational (date for instance) with
>FTS search will be efficient. Following your example, before look the FTS side, the
>optimizer will get the few ones that pass the date filter and then these ones will be FT
>searched. This improvement also will improve multiple CONTAINS queries, so you will
>not longer experiment pain.
When you say the "next FTS release," does this mean an upgrade to SQL
2005's FTS, or do you mean the FTS that is released in whatever version
comes after SQL Server 2005 (i.e. SQL Server 2010 ;-)

> -For now, the best you can do is to use computed columns. These columns will
>contain virtually the same data than the original columns and you can create a FT index
> on that column. The indexing time will take longer as you are merging 2 or more
>columns but at query time you will be able to query efficiently and find what you look
>for.
Can you give me an example of how to do a FTI on a computed column? We
are currently using SQL Server 2000, and only preparing our move to
2005, so are not yet familiar with 2005 completely.
Thanks,
Thomas

Does SQL 2005 purchase come with access to SQL 2000 media?

My company is planning to purchase a SQL2005 standard Processor license (unlimited users - about $5000)

Does anyone know if we can obtain SQL2000 media along with the purchase. Because of the app we are running and its stage of development, we have to install and run SQL2000 for about 6 months before we can run Sql2005. We dont want to purchase SQL2000 for such a short term use.

I asked a DELL rep to help me with this over a month ago and he still has no answers for me.

Any help with this would be greatly appreciated.

Already answered this question ton the setup & upgrade forum:

My previous response:

SQL Server 2000 is not provided with SQL Server 2005. Some components that ship with SQL Server 2005 will update existing SQL Server 2000 components to address any compatibility issues.

You have a couple of options:

* Use the SQL Server 2000 eval for the time being. Not sure if it is still available

* Use SQL Server 2000 MSDE, as it is a free download. Not sure if it is still available

* Use SQL Server 2005 but run the database(s) in SQL Server 2000 (version 8) compatibility mode.

Thanks,

Peter Saddow

|||

I believe that the SQL 2005 license covers using a downlevel version. However, for definitive answers to licensing questions, call the licensing folks at:

Licensing –VL Contact
(800) 426-9400

You will have to contact Microsoft Fullfillment to obtain the media, or if you have access to your MSDN subscription...

Does Sql 2005 allow ORDER BY @Variable ?

I am writing a stored proc and do not want to use dynamic sql. Does Sql
2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
doesn't work. My @.Variable is a VARCHAR(50).
Thanks!
Not directly. You could use a CASE structure to allow alternative orderings. Here is one idea:
USE Northwind
GO
DECLARE @.OrderVar varchar(20)
SET @.OrderVar = 'LastName'
SELECT
LastName,
FirstName
FROM Employees
ORDER BY CASE @.OrderVar
WHEN 'LastName' THEN LastName
WHEN 'FirstName' THEN FirstName
END ASC,
CASE @.OrderVar
WHEN 'LastName' THEN FirstName
WHEN 'FirstName' THEN LastName
END ASC
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Dan E" <dan_english2@.cox.net> wrote in message news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...
>I am writing a stored proc and do not want to use dynamic sql. Does Sql
> 2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
> doesn't work. My @.Variable is a VARCHAR(50).
> Thanks!
>
|||http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html
"Dan E" <dan_english2@.cox.net> wrote in message
news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...
>I am writing a stored proc and do not want to use dynamic sql. Does Sql
>2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
>doesn't work. My @.Variable is a VARCHAR(50).
> Thanks!
>

Does Sql 2005 allow ORDER BY @Variable ?

I am writing a stored proc and do not want to use dynamic sql. Does Sql
2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
doesn't work. My @.Variable is a VARCHAR(50).
Thanks!Not directly. You could use a CASE structure to allow alternative orderings.
Here is one idea:
USE Northwind
GO
DECLARE @.OrderVar varchar(20)
SET @.OrderVar = 'LastName'
SELECT
LastName,
FirstName
FROM Employees
ORDER BY CASE @.OrderVar
WHEN 'LastName' THEN LastName
WHEN 'FirstName' THEN FirstName
END ASC,
CASE @.OrderVar
WHEN 'LastName' THEN FirstName
WHEN 'FirstName' THEN LastName
END ASC
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Dan E" <dan_english2@.cox.net> wrote in message news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl..
.
>I am writing a stored proc and do not want to use dynamic sql. Does Sql
> 2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
> doesn't work. My @.Variable is a VARCHAR(50).
>
> Thanks!
>
>|||http://databases.aspfaq.com/databas...se
.html
"Dan E" <dan_english2@.cox.net> wrote in message
news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...
>I am writing a stored proc and do not want to use dynamic sql. Does Sql
>2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
>doesn't work. My @.Variable is a VARCHAR(50).
> Thanks!
>

Does RS process sleep?

I tried both SQL2000 & SQL2005.
But both servers RS processes sleep if no users access to them over an hour.
Once it sleeps, the user has to wait 20 seconds to start responding the
request.
Is this normal? Is there anyway to prevent this waiting time?
Please help!!What I do is have a very simple report that auto-refreshes every 5 minutes.
That keeps it alive. There is also an IIS configuration that can be set but
I haven't used it and I don't remember it off the top of my head. The
auto-refresh is pretty easy hack though.
Instead of 5 minutes you could set it longer than than (30 minutes?).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aki Nomura" <anomura@.jtb.com> wrote in message
news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>I tried both SQL2000 & SQL2005.
> But both servers RS processes sleep if no users access to them over an
> hour.
> Once it sleeps, the user has to wait 20 seconds to start responding the
> request.
> Is this normal? Is there anyway to prevent this waiting time?
> Please help!!
>
>|||Auto-refreshes means scheduling report?
Actually I tried an every 3 minutes schedule report.
But it didn't work for my case.
Anyway, I will try the IIS configuration.
Thanks.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
> What I do is have a very simple report that auto-refreshes every 5
> minutes. That keeps it alive. There is also an IIS configuration that can
> be set but I haven't used it and I don't remember it off the top of my
> head. The auto-refresh is pretty easy hack though.
> Instead of 5 minutes you could set it longer than than (30 minutes?).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Aki Nomura" <anomura@.jtb.com> wrote in message
> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>|||Layout, report tab, autorefreshes checkbox. Once you have a report that
autorefreshes then open it and leave it up. This has worked for me both with
RS 2000 and RS 2005.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aki Nomura" <anomura@.jtb.com> wrote in message
news:OzI$fDD$FHA.504@.TK2MSFTNGP09.phx.gbl...
> Auto-refreshes means scheduling report?
> Actually I tried an every 3 minutes schedule report.
> But it didn't work for my case.
> Anyway, I will try the IIS configuration.
> Thanks.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
>> What I do is have a very simple report that auto-refreshes every 5
>> minutes. That keeps it alive. There is also an IIS configuration that can
>> be set but I haven't used it and I don't remember it off the top of my
>> head. The auto-refresh is pretty easy hack though.
>> Instead of 5 minutes you could set it longer than than (30 minutes?).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Aki Nomura" <anomura@.jtb.com> wrote in message
>> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>>
>|||Yes, it works.
Thanks again.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:egpp%23KE$FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Layout, report tab, autorefreshes checkbox. Once you have a report that
> autorefreshes then open it and leave it up. This has worked for me both
> with RS 2000 and RS 2005.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Aki Nomura" <anomura@.jtb.com> wrote in message
> news:OzI$fDD$FHA.504@.TK2MSFTNGP09.phx.gbl...
>> Auto-refreshes means scheduling report?
>> Actually I tried an every 3 minutes schedule report.
>> But it didn't work for my case.
>> Anyway, I will try the IIS configuration.
>> Thanks.
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
>> What I do is have a very simple report that auto-refreshes every 5
>> minutes. That keeps it alive. There is also an IIS configuration that
>> can be set but I haven't used it and I don't remember it off the top of
>> my head. The auto-refresh is pretty easy hack though.
>> Instead of 5 minutes you could set it longer than than (30 minutes?).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Aki Nomura" <anomura@.jtb.com> wrote in message
>> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>>
>>
>|||That is called Application Pool in IIS. Go to IIS Manager, under Applocation
Pools node, right click "DefaultAppPool" in which the Reporting Server work
process is running, select properties. On "Performace" tag, you will see, by
default, the app pool will shut down if being idle for 20 min. You can
extend this time to 8x60min 480min, so that the app pool will not shut down
for a regular working day. However, the first report reader of the day, will
hit the delay. You may schedule a dummy report at beginning of a work day
for this.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
> What I do is have a very simple report that auto-refreshes every 5
> minutes. That keeps it alive. There is also an IIS configuration that can
> be set but I haven't used it and I don't remember it off the top of my
> head. The auto-refresh is pretty easy hack though.
> Instead of 5 minutes you could set it longer than than (30 minutes?).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Aki Nomura" <anomura@.jtb.com> wrote in message
> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>|||Wow, This is what I was looking for.
Thank you.
"Norman Yuan" <NotReal@.NotReal.not> wrote in message
news:uSWtdkQ$FHA.532@.TK2MSFTNGP15.phx.gbl...
> That is called Application Pool in IIS. Go to IIS Manager, under
> Applocation Pools node, right click "DefaultAppPool" in which the
> Reporting Server work process is running, select properties. On
> "Performace" tag, you will see, by default, the app pool will shut down if
> being idle for 20 min. You can extend this time to 8x60min 480min, so that
> the app pool will not shut down for a regular working day. However, the
> first report reader of the day, will hit the delay. You may schedule a
> dummy report at beginning of a work day for this.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
>> What I do is have a very simple report that auto-refreshes every 5
>> minutes. That keeps it alive. There is also an IIS configuration that can
>> be set but I haven't used it and I don't remember it off the top of my
>> head. The auto-refresh is pretty easy hack though.
>> Instead of 5 minutes you could set it longer than than (30 minutes?).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Aki Nomura" <anomura@.jtb.com> wrote in message
>> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>>
>

Does MS know SQL2005 query performance is slower than SQL2000?

Hi,

I tested a simple query like Select colA from TableB where colC= 'XX' with SQL2000 and SQL2005.

Of course, Index is same, number of records are same.

After I execute that query and checked it with profiler. SQL2000 just took 18 ms but SQL2005 took 118 ms in my environment. Actually, the machine that is installed SQL2005 has better H/W spec than SQL2000.

I could not belive that so I tested several times but SQL2005 was slow.

After I searched this forum, I found that some guys had same problem with SQL2005 performance. I rebuilt index in SQL2005 but didn't work.

Even though I am using SP2, it is still slow than SQL2000.

Am I missing somthing? I could not understand how it could happen.

Does anybody have any solution?

Thank you in advance

James

did you clear the proc cache before running the procedures? Running the commands below will ensure that you are running both sprocs on common ground:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Also, you can't always go by the time it takes to run a query. You really need to compare the logical reads returned by running the command SET STATISTICS IO ON before you run the statements to compare the reads. If your reads are drastically different, something may be funky.

Tim|||

Tim,

Thank you for answer.

Actually, before I tested, I restarted all services so it was not a problem of cache.

In addition, reads in profile log of SQL2005 is more than SQL2000 which is not strange based on the result.

Funny thing is speed is smiliar after data is cached. This problem happened when I tried data from disk.

I am not sure what is wrong.

I should discuss it with MS support soon.

Thank you

James

|||After you upgraded the database to SQL 2005 did you update the stats or rebuild the indexes? It's recommended to update the stats on the tables and indexes after upgrading to SQL 2005 to get proper query plans in SQL 2005.|||

Thank you Denny for replying

Unfortunately, it didn't work

Actually, I didn't migrate DB from SQL2000. I just created exactly same DB as SQL2000.

100ms is not a big deal for a SQL statement but if a stored procedure has 1000 sql statements. It will be 100,000ms which is a big.

This can explain why same stored procedure is slower than SQL2000.

James.

|||

Try optimising the data disks:

1) Set the disk to Basic disk

2) If the disk subsystem is RAID, set the stripe size to 64k, and the controller cache to 100% write.

3) Using DISKPART, create the partition using the command CREATE PARTITION PRIMARY ALIGN=64

4) Format the data disk with a cluster size of 64k.

Now you're ready to go from the disk side of things!

|||Does the execution plans show the SQL Servers taking the same path to the data? Where do the cost differences show up?|||

Danny and BigE

Thank you for replying.

Well. Execution plan is exactly same. I am not sure where it comes from.

As I said the machine that has SQL2005 is better H/W spec so I don't think it is a problem of H/W as BigE said.

I might try to do as BigE suggested but I could not agree we should set this for running SQL2005.

Think about that. SQL2005 is advanced version than SQL2000 which is 7 years ago!! Why does user consider about those kinds of disk setting? Even though it is ture, what is the big benefit of upgrading to user who is using small or medium application?

Anyway, If you have two machine that has SQL2000 and SQL2005, just try a select statement and check read and duration.

You will notice what I am saying..

James.|||

Hi James,

Did you get the solution as you described?

I have same problem and I cannot find any solutions.

Please help.

Clara

|||

Thank you BigE

I think your suggestion might improve performance but here is my concern about using SQL2005.

If it is a problem of Disk speed, Why does MS provide a fucntion to make DATABASE on top of the MEMORY DISK?

In other words, MS can create MEMORY DISK DATABASE in SQL2008 for better performance !

Maybe they will say to me that I am crazy but... If you can use UPS, then SQL server can dump that memory database to Disk during UPS is working.

Anyway, I could not buy that reason becaue , As I said, the SQL is running slower server than SQL2005.

By copying Clara,

Sorry, I could not find the solution yet. One of MS consultant that I know gave to me some suggestion but it doesn't work.

Maybe I should try SQL2008 CTP instead of SQL2005

Regards,

James Lim

Does MS know SQL2005 query performance is slower than SQL2000?

Hi,

I tested a simple query like Select colA from TableB where colC= 'XX' with SQL2000 and SQL2005.

Of course, Index is same, number of records are same.

After I execute that query and checked it with profiler. SQL2000 just took 18 ms but SQL2005 took 118 ms in my environment. Actually, the machine that is installed SQL2005 has better H/W spec than SQL2000.

I could not belive that so I tested several times but SQL2005 was slow.

After I searched this forum, I found that some guys had same problem with SQL2005 performance. I rebuilt index in SQL2005 but didn't work.

Even though I am using SP2, it is still slow than SQL2000.

Am I missing somthing? I could not understand how it could happen.

Does anybody have any solution?

Thank you in advance

James

did you clear the proc cache before running the procedures? Running the commands below will ensure that you are running both sprocs on common ground:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Also, you can't always go by the time it takes to run a query. You really need to compare the logical reads returned by running the command SET STATISTICS IO ON before you run the statements to compare the reads. If your reads are drastically different, something may be funky.

Tim|||

Tim,

Thank you for answer.

Actually, before I tested, I restarted all services so it was not a problem of cache.

In addition, reads in profile log of SQL2005 is more than SQL2000 which is not strange based on the result.

Funny thing is speed is smiliar after data is cached. This problem happened when I tried data from disk.

I am not sure what is wrong.

I should discuss it with MS support soon.

Thank you

James

|||After you upgraded the database to SQL 2005 did you update the stats or rebuild the indexes? It's recommended to update the stats on the tables and indexes after upgrading to SQL 2005 to get proper query plans in SQL 2005.|||

Thank you Denny for replying

Unfortunately, it didn't work

Actually, I didn't migrate DB from SQL2000. I just created exactly same DB as SQL2000.

100ms is not a big deal for a SQL statement but if a stored procedure has 1000 sql statements. It will be 100,000ms which is a big.

This can explain why same stored procedure is slower than SQL2000.

James.

|||

Try optimising the data disks:

1) Set the disk to Basic disk

2) If the disk subsystem is RAID, set the stripe size to 64k, and the controller cache to 100% write.

3) Using DISKPART, create the partition using the command CREATE PARTITION PRIMARY ALIGN=64

4) Format the data disk with a cluster size of 64k.

Now you're ready to go from the disk side of things!

|||Does the execution plans show the SQL Servers taking the same path to the data? Where do the cost differences show up?|||

Danny and BigE

Thank you for replying.

Well. Execution plan is exactly same. I am not sure where it comes from.

As I said the machine that has SQL2005 is better H/W spec so I don't think it is a problem of H/W as BigE said.

I might try to do as BigE suggested but I could not agree we should set this for running SQL2005.

Think about that. SQL2005 is advanced version than SQL2000 which is 7 years ago!! Why does user consider about those kinds of disk setting? Even though it is ture, what is the big benefit of upgrading to user who is using small or medium application?

Anyway, If you have two machine that has SQL2000 and SQL2005, just try a select statement and check read and duration.

You will notice what I am saying..

James.|||

Hi James,

Did you get the solution as you described?

I have same problem and I cannot find any solutions.

Please help.

Clara

|||

Thank you BigE

I think your suggestion might improve performance but here is my concern about using SQL2005.

If it is a problem of Disk speed, Why does MS provide a fucntion to make DATABASE on top of the MEMORY DISK?

In other words, MS can create MEMORY DISK DATABASE in SQL2008 for better performance !

Maybe they will say to me that I am crazy but... If you can use UPS, then SQL server can dump that memory database to Disk during UPS is working.

Anyway, I could not buy that reason becaue , As I said, the SQL is running slower server than SQL2005.

By copying Clara,

Sorry, I could not find the solution yet. One of MS consultant that I know gave to me some suggestion but it doesn't work.

Maybe I should try SQL2008 CTP instead of SQL2005

Regards,

James Lim

Does MS know SQL2005 query performance is slower than SQL2000?

Hi,

I tested a simple query like Select colA from TableB where colC= 'XX' with SQL2000 and SQL2005.

Of course, Index is same, number of records are same.

After I execute that query and checked it with profiler. SQL2000 just took 18 ms but SQL2005 took 118 ms in my environment. Actually, the machine that is installed SQL2005 has better H/W spec than SQL2000.

I could not belive that so I tested several times but SQL2005 was slow.

After I searched this forum, I found that some guys had same problem with SQL2005 performance. I rebuilt index in SQL2005 but didn't work.

Even though I am using SP2, it is still slow than SQL2000.

Am I missing somthing? I could not understand how it could happen.

Does anybody have any solution?

Thank you in advance

James

did you clear the proc cache before running the procedures? Running the commands below will ensure that you are running both sprocs on common ground:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Also, you can't always go by the time it takes to run a query. You really need to compare the logical reads returned by running the command SET STATISTICS IO ON before you run the statements to compare the reads. If your reads are drastically different, something may be funky.

Tim|||

Tim,

Thank you for answer.

Actually, before I tested, I restarted all services so it was not a problem of cache.

In addition, reads in profile log of SQL2005 is more than SQL2000 which is not strange based on the result.

Funny thing is speed is smiliar after data is cached. This problem happened when I tried data from disk.

I am not sure what is wrong.

I should discuss it with MS support soon.

Thank you

James

|||After you upgraded the database to SQL 2005 did you update the stats or rebuild the indexes? It's recommended to update the stats on the tables and indexes after upgrading to SQL 2005 to get proper query plans in SQL 2005.|||

Thank you Denny for replying

Unfortunately, it didn't work

Actually, I didn't migrate DB from SQL2000. I just created exactly same DB as SQL2000.

100ms is not a big deal for a SQL statement but if a stored procedure has 1000 sql statements. It will be 100,000ms which is a big.

This can explain why same stored procedure is slower than SQL2000.

James.

|||

Try optimising the data disks:

1) Set the disk to Basic disk

2) If the disk subsystem is RAID, set the stripe size to 64k, and the controller cache to 100% write.

3) Using DISKPART, create the partition using the command CREATE PARTITION PRIMARY ALIGN=64

4) Format the data disk with a cluster size of 64k.

Now you're ready to go from the disk side of things!

|||Does the execution plans show the SQL Servers taking the same path to the data? Where do the cost differences show up?|||

Danny and BigE

Thank you for replying.

Well. Execution plan is exactly same. I am not sure where it comes from.

As I said the machine that has SQL2005 is better H/W spec so I don't think it is a problem of H/W as BigE said.

I might try to do as BigE suggested but I could not agree we should set this for running SQL2005.

Think about that. SQL2005 is advanced version than SQL2000 which is 7 years ago!! Why does user consider about those kinds of disk setting? Even though it is ture, what is the big benefit of upgrading to user who is using small or medium application?

Anyway, If you have two machine that has SQL2000 and SQL2005, just try a select statement and check read and duration.

You will notice what I am saying..

James.|||

Hi James,

Did you get the solution as you described?

I have same problem and I cannot find any solutions.

Please help.

Clara

|||

Thank you BigE

I think your suggestion might improve performance but here is my concern about using SQL2005.

If it is a problem of Disk speed, Why does MS provide a fucntion to make DATABASE on top of the MEMORY DISK?

In other words, MS can create MEMORY DISK DATABASE in SQL2008 for better performance !

Maybe they will say to me that I am crazy but... If you can use UPS, then SQL server can dump that memory database to Disk during UPS is working.

Anyway, I could not buy that reason becaue , As I said, the SQL is running slower server than SQL2005.

By copying Clara,

Sorry, I could not find the solution yet. One of MS consultant that I know gave to me some suggestion but it doesn't work.

Maybe I should try SQL2008 CTP instead of SQL2005

Regards,

James Lim

Does anyone have sql2005 EE running on 64 bit Itanium server?

I don't think mine is using all of the memory it could on the server. I've read that the Task Manager does not give you an accurate measure of the memory usage but 130 MB max does not seem right on a 12 GB server. I've set the minimum to 2 GB in SSMS. The account that is running sqlservice has Lock Pages in Memory privileges. I've turned on AWE in SSMS just to be safe. We are experiencing high CPU usage and my theory is that it is caused my unnecessary I/O due to not being able to cache. The serve has Windows 2003 sp1. The SQL2005 Buffer Manager AWE Lookup maps\sec, stolen maps\sec, unmap calls/sec, unmap pages/sec, write maps/sec are all 0.

Any help would be appreciated. Below is the out from DBCC MEMORYSTATUS. This server also has an instance of sql2000 EE on it with max memory set to 5 GB and it seems to be running fine.

Memory Manager KB

--

VM Reserved 12687344

VM Committed 87304

AWE Allocated 3211008

Reserved Memory 1024

Reserved Memory In Use 0

(5 row(s) affected)

Memory node Id = 0 KB

--

VM Reserved 12681712

VM Committed 81752

AWE Allocated 3211008

MultiPage Allocator 19864

SinglePage Allocator 272960

(5 row(s) affected)

MEMORYCLERK_SQLGENERAL (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 4240

MultiPage Allocator 4984

(7 row(s) affected)

MEMORYCLERK_SQLBUFFERPOOL (Total) KB

- --

VM Reserved 12615680

VM Committed 16384

AWE Allocated 3211008

SM Reserved 0

SM Commited 0

SinglePage Allocator 0

MultiPage Allocator 1128

(7 row(s) affected)

MEMORYCLERK_SQLQUERYEXEC (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 88

MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLOPTIMIZER (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 672

MultiPage Allocator 96

(7 row(s) affected)

MEMORYCLERK_SQLUTILITIES (Total) KB

- --

VM Reserved 240

VM Committed 240

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 320

MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSTORENG (Total) KB

- --

VM Reserved 3904

VM Committed 3904

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 1280

MultiPage Allocator 472

(7 row(s) affected)

MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 728

MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLCLR (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 8

MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKER (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 112

MultiPage Allocator 320

(7 row(s) affected)

MEMORYCLERK_SQLHTTP (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 8

MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SNI (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 72

MultiPage Allocator 16

(7 row(s) affected)

MEMORYCLERK_FULLTEXT (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 16

MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLXP (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 16

MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_BHF (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 56

MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLQERESERVATIONS (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 331616

MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_HOST (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 16

MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SOSNODE (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 3416

MultiPage Allocator 11360

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 48

MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_OBJCP (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 69400

MultiPage Allocator 672

(7 row(s) affected)

CACHESTORE_SQLCP (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 166824

MultiPage Allocator 32

(7 row(s) affected)

CACHESTORE_PHDR (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 17944

MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XPROC (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 144

MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_TEMPTABLES (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 32

MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_NOTIF (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 16

MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_VIEWDEFINITIONS (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 16

MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBTYPE (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 8

MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBELEMENT (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 8

MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBATTRIBUTE (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 8

MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_STACKFRAMES (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 0

MultiPage Allocator 8

(7 row(s) affected)

CACHESTORE_BROKERTBLACS (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 456

MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERKEK (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 8

MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERDSH (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 8

MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 8

MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERRSB (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 16

MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERREADONLY (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 96

MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERTO (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 8

MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_EVENTS (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 16

MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_SYSTEMROWSET (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 1784

MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SCHEMAMGR (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 2192

MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_DBMETADATA (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 2800

MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_TOKENPERM (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 872

MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_OBJPERM (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 256

MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SXC (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 168

MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LBSS (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 32

MultiPage Allocator 544

(7 row(s) affected)

OBJECTSTORE_SNI_PACKET (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 1824

MultiPage Allocator 48

(7 row(s) affected)

OBJECTSTORE_SERVICE_BROKER (Total) KB

- --

VM Reserved 0

VM Committed 0

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 288

MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LOCK_MANAGER (Total) KB

- --

VM Reserved 8192

VM Committed 8192

AWE Allocated 0

SM Reserved 0

SM Commited 0

SinglePage Allocator 20856

MultiPage Allocator 0

(7 row(s) affected)

Buffer Distribution Buffers

--

Stolen 13626

Free 28271

Cached 33153

Database (clean) 315604

Database (dirty) 2557

I/O 0

Latched 5

(7 row(s) affected)

Buffer Counts Buffers

--

Committed 393216

Target 393216

Hashed 318166

Stolen Potential 297983

External Reservation 28759

Min Free 64

Visible 393216

Available Paging File 2528231

(8 row(s) affected)

Procedure Cache Value

--

TotalProcs 2358

TotalPages 31877

InUsePages 425

(3 row(s) affected)

Global Memory Objects Buffers

--

Resource 257

Locks 2610

XDES 42

SETLS 2

SE Dataset Allocators 4

SubpDesc Allocators 2

SE SchemaManager 273

SQLCache 505

Replication 2

ServerGlobal 48

XP Global 2

SortTables 2

(12 row(s) affected)

Query Memory Objects Value

--

Grants 1

Waiting 0

Available (Buffers) 241218

Maximum (Buffers) 282670

Limit 282670

Next Request 0

Waiting For 0

Cost 0

Timeout 0

Wait Time 0

Last Target 295470

(11 row(s) affected)

Small Query Memory Objects Value

--

Grants 0

Waiting 0

Available (Buffers) 12800

Maximum (Buffers) 12800

Limit 12800

(5 row(s) affected)

Optimization Queue Value

--

Overall Memory 2581880832

Target Memory 2209619968

Last Notification 1

Timeout 6

Early Termination Factor 5

(5 row(s) affected)

Small Gateway Value

--

Configured Units 8

Available Units 8

Acquires 0

Waiters 0

Threshold Factor 500000

Threshold 500000

(6 row(s) affected)

Medium Gateway Value

--

Configured Units 2

Available Units 2

Acquires 0

Waiters 0

Threshold Factor 12

(5 row(s) affected)

Big Gateway Value

--

Configured Units 1

Available Units 1

Acquires 0

Waiters 0

Threshold Factor 8

(5 row(s) affected)

MEMORYBROKER_FOR_CACHE Value

-- --

Allocations 33157

Rate 2

Target Allocations 301911

Future Allocations 0

Last Notification 1

(5 row(s) affected)

MEMORYBROKER_FOR_STEAL Value

-- --

Allocations 963

Rate 14

Target Allocations 269729

Future Allocations 0

Last Notification 1

(5 row(s) affected)

MEMORYBROKER_FOR_RESERVE Value

-- --

Allocations 41452

Rate 21351

Target Allocations 315171

Future Allocations 70667

Last Notification 1

(5 row(s) affected)

What "edition" of 2003 is installed. You need to have Enterprise or Data Center installed for 12gb of RAM. See http://www.microsoft.com/windowsserver2003/evaluation/features/comparefeatures.mspx.

Did you set PAE in the Windows boot.ini? If you right click on "My Computer" and select "Properties", how much memory does it show and does it say "Physical Address Extension" after the RAM?

2005 only allocates memory when needed, instead of 2000 which just grabs it all when the service starts.|||

win 2003 EE sp1.

The server doesn't seem to have a boot.ini file. Does 64 bit itanium servers usually have one?

The my computer reading is 11.9 GB of Ram. It does not say "Physical Address Extendsions"

The sql2000 instance is using 6 GB of ram so AWE should be working but it isn't for sql2005.

We use a lot of table variable in 2005 that are stored in memory if it is available an I think sql is thinking there isn't any and writing them to the tempdb instead.

Thanks for responding to my post

|||I forgot in 64bit you don't set PAE, that is only a 32 bit problem.

In SQL 2005 tempdb usage was completely changed. It now stores a lot of temp data that 2000 had in RAM, in tempdb.

See: http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx|||I read that one yesterday. "System" has locked pages in memory rights. Our instance is running under "Local System" so it should be doing this, right.|||I talked to someone at Microsoft and they said to ignore Task Manager reading (nice). You can tell SQL memory usage by using Performance Monitor object SQL Memory Management counters "Target Server Memory" and "Total Server Memory". So, unless you are a DBA and know about this Task Manager misleads you.

do you trust the DTA?

Hey all! I am fairly new using sql. I have been put in charge of managing our sql2005 databases. Currently we are having some deadlocking issues. I turned on the trace flag 1222 and found a couple of queries that were deadlocking, I ran both through the DTA. One was a SP and the other was a ad hoc, I copied the queries from the trace flag output into the dta, after receiving a few errors the queries went through...one at a time of course. The first one show nothing but the second query show that it needed to create about 9 indexes and 50 some odd statistics. I just couldn't believe it. Does this sounds right to anyone? Please let me know if you need more detail.

thanks,

Have you checked the current queries execution plan for such slower performance.

What is the index strategy on this table?

DTA one way its good and one way you should be cautious about what its referring, without performing any tests there is no such straight solution.

Do we really need query based optimisation?

Hello,

I was playing with the Query based optimisation in SQL2005.

- it is not available by default, you have to enable the query log in the server properties

- it generates 1 out of 10 queries log entry

- 1 out of 10 query is still a large number of log entries if you have many users and many cubes, 1 out of 10 does not filter any redundant queries.

- I also tried to use this mechanism to create an usage audit log, Log table becomes bloated very quickly.

- tried to optimise the partitions based on this mechanism and did not see any performance boost.

I wonder in which cases this option really makes a difference.

I went through all the dimensions design best practices I could find and I finally came to the conclusion that my front-end tool of predilection (Excel 2003) is the problem.

Not sure if anyone did a test on speed boost of aggregations optimization when using the new Excel 2007 as a front end but even without such optimization, Excel 2007 executes in miliseconds what takes ages in Excel 2003 no matter what optimization.

What is the real advantage of using query based optimisations in ssas 2005?

Since this option is disabled by default, I doubt that it would go any farther than an academic type of optimisation. With the right client, 20 ms un-optimized vs 12 ms optimized would certainly not make a difference in the eyes of an end user.

Also, you tend to loose the optimizations each time you change your cube structure and then have to wait a few weeks untill you have a sample of queries large enough.

Any thoughts?

Philippe

I agree with your doubts regarding query based optimization. In AS2000 I have seen that it delivers improvements. Some MS people in this newsgroup have hinted that we will see improvements in SP2 for SSAS2005.

Regards

Thomas Ivarsson

|||

Dont agree with you guys a bit. Usage based optimization is very useful. Especially in AS 2005.

First to answer some questions:

>>- it is not available by default, you have to enable the query log in the server properties

Yes it is not avaliable by default. Here is whitepaper explaning query log setup and options controlling it: http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/config_ssas_querylog.mspx

>>- it generates 1 out of 10 queries log entry

Take a look at the whitepaper and you'll see the QueryLogSampling property that defiles the frequency of sampling

>>- 1 out of 10 query is still a large number of log entries if you have many users and many cubes, 1 out of 10 does not filter any redundant queries.

The way you should think of Usage Based Optimization is; You should let it run for awhile and then used it to design aggregatins. After that if you satisfied with UBO, you can stop it.

>>- I also tried to use this mechanism to create an usage audit log, Log table becomes bloated very quickly.

Not sure what you refer to here

>>- tried to optimise the partitions based on this mechanism and did not see any performance boost.

This could be the indication that you dont really need usage based optimization. If your query perofrmance problem could be solved by moving to use another tool, this is good indication, you probably dont need it.

With lots of attributes and poorly designed attribute relationships your aggregations are going to be of little help. Put decent size of data into your cube and see perofrmance going down. Collecting stats and than later designig aggregations for these queries is probably the only way to go in this situation.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thank you Edward for the clarification.

By Usage audit I meant I did set the log sampling to 1 out of 1 so I can query the OlapQueryLog table to report on cube usage.

That is a management requirement to see who uses which cube and how frequently.

The log mechanism is not designed to do this however it is the only way I have found to provide this usage information.

If there is a better way, I would be glad to use it. All I need is UserID, database name, Cube name, Date. If I could have the query itself also, that could be a nice thing. I do not need the subcube code.

These days of SOX audits makes it very interesting to be able to tell who is actually using your systems beyond simply providing a list of authorized users.

Regards,
Philippe|||

For audit purposes you can create a server-side trace. Try to see how in SQL Profiler you can create a trace that writes to a file. This way you should get UserID and all other properties logged.

You only need few events selected for this trace for instance: Query Begin, Command Begin, Audit Login, Audit Logout

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.