Bug in SELECT IN SELECT?

Hello,

we had a performance issue with our application. The application executed a lot of SELECT IN SELECT-Statements.

Because SQL-Server was at 100% the whole time we started the profiler and had a look. A single one of these SELECT IN SELECT-Statements had a duration from 4 to 6 seconds. That looked strange, so we tried it without our application. Executing the SELECT IN SELECT-Statement directly: 4-6 seconds. Very strange about this is, that the second SELECT (the one after IN) returned only one or two items. So we splitted it: Executed the second SELECT first. Then executed the first statement with manually changed IN-clause. Both selects didn't need any noticable time. So, back in our application we did the same. The result was that the process that took about 70 minutes with the SELECT IN SELECT run through in under 3 minutes with result = SELECT; SELECT in result.

Pretty strange, hu?

Is this a known bug? Is this a bug at all?

the query plan might give some clues to the bad performance. also, you might consider converting your IN() query into an Inner join.

buffer cache hit ratio...how to measure?

Does anyone know how to measure the buffer cache hit ratio? I have been reading a lot about it but can't find this measurement in Performance Monitor.
ThanksNevermind, I found the counter. For anyone else who may be interested, it is in the performance monitor under SQL Server: Buffer Manager.

Thanks

Bottleneck - Backup Server

I am trying to analyze/identify the bottleneck on our new backup
server. I was wondering if someone could recommend some performance
counters to watch.
Right now I am watching "% Write Time" I am averaging around
"500" for this measure while copying a 50GB database file. It is
taking almost twice as long to copy the file to a different server as
opposed to a different direct attached array. The % disk write counter
is similar on both servers. Am I wrong to assume that the physical
disks are the bottleneck (due to high performance counter ratings)?
Does anyone know some general guide lines on real world throughput on
the following? What kinds of things should I be looking at and/or
asking our IT department?
Gigabit dedicated network
Perc4 (I read on Dells site that it is 320MB/s which translates to
1.1TB / hour)
Raid 5 using 6 - 300MB disks (10,000 RPM)
Other bottleneck candidate?
Server Configuration
Windows Server 2003
Dell PowerEdge 2850
Perc4 - Raid 5
PowerVault 200s
Gigabit networkDave,
Look at the current or average disk queue length. It will tell you if the OS
is waiting for the disks. Also look at the network interface / Bytes
Receive/sec. to determine how fast the data is being moved across your
network. The network is usually the slowest in the mix and it is common for
a server to server copy to take more time than a disk to disk copy on the
same server.
Also, you will have additional overhead writing to a raid 5 because of
writing the parity information.
-- Bill
"Dave" <daveg.01@.gmail.com> wrote in message
news:1168620575.775759.196740@.i15g2000cwa.googlegroups.com...
>I am trying to analyze/identify the bottleneck on our new backup
> server. I was wondering if someone could recommend some performance
> counters to watch.
> Right now I am watching "% Write Time" I am averaging around
> "500" for this measure while copying a 50GB database file. It is
> taking almost twice as long to copy the file to a different server as
> opposed to a different direct attached array. The % disk write counter
> is similar on both servers. Am I wrong to assume that the physical
> disks are the bottleneck (due to high performance counter ratings)?
>
> Does anyone know some general guide lines on real world throughput on
> the following? What kinds of things should I be looking at and/or
> asking our IT department?
>
> Gigabit dedicated network
> Perc4 (I read on Dells site that it is 320MB/s which translates to
> 1.1TB / hour)
> Raid 5 using 6 - 300MB disks (10,000 RPM)
> Other bottleneck candidate?
>
> Server Configuration
> Windows Server 2003
> Dell PowerEdge 2850
> Perc4 - Raid 5
> PowerVault 200s
> Gigabit network
>

Boots Performance

Hi ,
I already create clustered and non clustered index in order to improve the
reports performance. But after I test several times on my reports , the
processing time almost the same as before I index the table.
Any other method to increase the performance ? Can I index the "view" I
created ?
Travis Tan
Travis,
The group will need substantially more information in order to help diagnose
your performance issue. The basic answers to your questions are Yes. and
most likely yes depending on the version you are running.
Please post more information including:
SQL Server version
Basic hardware configuration
The query
The estimated query plan
What is the database used for (OLTP, DSS, Reporting, etc.)
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:5A01556F-70F4-4CC4-9779-2CAB8D4710F4@.microsoft.com...
> Hi ,
> I already create clustered and non clustered index in order to improve
> the
> reports performance. But after I test several times on my reports , the
> processing time almost the same as before I index the table.
> Any other method to increase the performance ? Can I index the "view" I
> created ?
>
> --
> Travis Tan

Boots Performance

Hi ,
I already create clustered and non clustered index in order to improve the
reports performance. But after I test several times on my reports , the
processing time almost the same as before I index the table.
Any other method to increase the performance ? Can I index the "view" I
created ?
Travis TanTravis,
The group will need substantially more information in order to help diagnose
your performance issue. The basic answers to your questions are Yes. and
most likely yes depending on the version you are running.
Please post more information including:
SQL Server version
Basic hardware configuration
The query
The estimated query plan
What is the database used for (OLTP, DSS, Reporting, etc.)
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:5A01556F-70F4-4CC4-9779-2CAB8D4710F4@.microsoft.com...
> Hi ,
> I already create clustered and non clustered index in order to improve
> the
> reports performance. But after I test several times on my reports , the
> processing time almost the same as before I index the table.
> Any other method to increase the performance ? Can I index the "view" I
> created ?
>
> --
> Travis Tan

Boosting up Fuzzy Lookups performance

Hello there,

Is there any way to increase the speed of search while performing fuzzy lookups against a 300,000 row Table ?

include a index for the column

Boost SQL Server priority on windows 2003

Hi,

I've noticed declining performance on our main accounting server. We have a Windows 2003 server running SQL 2000 Enterprise and 8 GBs of RAM.

Has anyone used the "boost SQL Server priority on windows" before? If so, were there any major benefits in using it?

This server is excusively used for SQL btw...

Thanks,
DJI run all of my servers with this option. Check the SQL BOL for Priority Boost Option. Just be sure it is a dedicated SQL server and that it is running SMP (ie, multiple processors).

Regards,

hmscott|||Thanks hmscott. I'll try it out tonight. Hope it works. I'm also going to put the tempdb on a totally different RAID (1 or 10) controlled drive on that server. Apparently that helps as well.|||On re-reading your post, I noted that you started by saying you've seen declining performance on your server. Don't expect priority boost to fix this for you. You probably have other issues in the underlying database. Priority boost just increases the thread priority for the SQLSERVR process (from 7 to 13). That won't yield major performance enhancements on a box that is already dedicated to SQL.

Regards,

hmscott