question regarding slow performance an PAGEIOLATCH_SH locks on sql2005

Hi,
I wondered in anyone can help with the following problem that i'm
experiencing, i'll try to provide as much info as possible and any
suggestions would be appreciated.
I have just started at an organsiation and there seems to be slow
performance maybe on the san on a 64bit itanium dual core machine. 4
CPUs are being showed to sql server, it also has 16gb of RAM. I'll
start with the configuration of the SAN.
After speaking to the SAN guy, rather than carve the SAN up into
different area's for san Logs/Data etc they have gone for the approach
of spreading a Vdisk across as many spindles as possible (All 145 of
them). So the area that is presented to the SQL Server according the
the SAN guys is a vraid 5 stripe made up of all 145 disks which are
all 72gb fibre-channel disks.
This storage is not just made available to sql server but also made
available to other apps as well that need storage. Having read the
manufactres best practice on setting this up there is a valid argument
for doing this.
The bandwidth from the SAN is 2Gb fibre, with each computer that uses
the SAN having 2Gb fibre cards.
Clearly, that could act as a bottle-neck. But, there's nothing that
can be done about it according to the SAN guy.
Needless to say, any changes on the SAN are pretty much going to be
out of the question as far as he's concerned but i think performance
isn't that good for the type of box they have and the SAN its attached
to.
The 2nd thing i'll explain is the setup of the database in question,
firstly whoever set it up split the database into 16 different file of
4 filegroups so the table that i'm selecting to is in one filegroup
split over 4 files and the the table selecting from is in another
filegroup made up of another 4 files. These are placed on the same
physical disk made up of the SAN LUN with 145 spindles.
Anyway when i do a select from a sales table which has various group
bys and then insert the results into a blank table with no indexes it
can take over 2hours for 200k rows which i find very slow.
When i look at the sysprocesses table i am getting various waits as
follows :-
72427200x0042900PAGEIOLATCH_SH 6:9:2192094
72427200x00690SLEEP_TASK
72427200x00000SOS_SCHEDULER_YIELD
The process seams to be going inbetween a PAGEIOLATCH and
SOS_SCHEDULER_YIELD a few times per second.
Running the following to get io stalls gives the following :-
Select * from sys.dm_io_virtual_file_stats (6,7)
Select * from sys.dm_io_virtual_file_stats (6,8)
Select * from sys.dm_io_virtual_file_stats (6,9)
Select * from sys.dm_io_virtual_file_stats (6,10)
gives results like :-
67170853985015624218246512844829457222526431245540454412438340
307010565449074954240x0000000000000954
It worries me that when the process is on the PAGEIOLATCH the wait
can be over 1000. Is it normal for the wait to be this long and what
would be the best way to prove one way or another if the configuration
of the san is causing this kind of performance?
Thanks for any suggestions in advance
Ian.
The SAN setup is not necessarily a problem. Spreading the I/Os over as many
spindles as possible is in general a good approach.
There are three possible causes: (1) your SQL query is not efficient (e.g.
bad plan), (2) you are I/O throughput limited, and (3) the performance is
killed by the I/O latency problem. Point 3 is unlikely as relevant as Point 2
in this case because you query appears to be dealing a large data set.
I'd first check if an inefficient parallel plan is being used. Assume that
your I/O subsystem can do ~200MB/sec, it shouldbe able to pull in over a
terabyte of data in 2 hours unless the query plan is such that it results in
smaller I/Os or not stressing I/O at all. A bad parallelism plan can do that
to you.
You can check several perfmon counters to get a feel whether the I/O
subsystem is saturated, in particular: Avg. Disk Bytes/Read and Avg. Disk
sec/Read. The former tells you how much throughput you are using and the
latter tells you how large is each read. To efficiently process
reporting-type of queries, the latter should be relatively large. Otherwise,
the former will not be good. Ideally, the latter should be larger than 64K
and the fomer should be close to 200MB/sec.
To be sure about what kind of throughput you can get from the drive
presented from your SAN, you should run some tests. If you have a chance, try
a simple table scan on a large and wide table to see how many MBs/sec you
achieve.
BTW, are you using a sinle 2Gb card or two load-balanced 1Gb cards?
Linchi
"ianwr" wrote:

> Hi,
> I wondered in anyone can help with the following problem that i'm
> experiencing, i'll try to provide as much info as possible and any
> suggestions would be appreciated.
> I have just started at an organsiation and there seems to be slow
> performance maybe on the san on a 64bit itanium dual core machine. 4
> CPUs are being showed to sql server, it also has 16gb of RAM. I'll
> start with the configuration of the SAN.
> After speaking to the SAN guy, rather than carve the SAN up into
> different area's for san Logs/Data etc they have gone for the approach
> of spreading a Vdisk across as many spindles as possible (All 145 of
> them). So the area that is presented to the SQL Server according the
> the SAN guys is a vraid 5 stripe made up of all 145 disks which are
> all 72gb fibre-channel disks.
> This storage is not just made available to sql server but also made
> available to other apps as well that need storage. Having read the
> manufactres best practice on setting this up there is a valid argument
> for doing this.
> The bandwidth from the SAN is 2Gb fibre, with each computer that uses
> the SAN having 2Gb fibre cards.
> Clearly, that could act as a bottle-neck. But, there's nothing that
> can be done about it according to the SAN guy.
> Needless to say, any changes on the SAN are pretty much going to be
> out of the question as far as he's concerned but i think performance
> isn't that good for the type of box they have and the SAN its attached
> to.
> The 2nd thing i'll explain is the setup of the database in question,
> firstly whoever set it up split the database into 16 different file of
> 4 filegroups so the table that i'm selecting to is in one filegroup
> split over 4 files and the the table selecting from is in another
> filegroup made up of another 4 files. These are placed on the same
> physical disk made up of the SAN LUN with 145 spindles.
> Anyway when i do a select from a sales table which has various group
> bys and then insert the results into a blank table with no indexes it
> can take over 2hours for 200k rows which i find very slow.
> When i look at the sysprocesses table i am getting various waits as
> follows :-
> 72427200x0042900PAGEIOLATCH_SH 6:9:2192094
> 72427200x00690SLEEP_TASK
> 72427200x00000SOS_SCHEDULER_YIELD
> The process seams to be going inbetween a PAGEIOLATCH and
> SOS_SCHEDULER_YIELD a few times per second.
> Running the following to get io stalls gives the following :-
> Select * from sys.dm_io_virtual_file_stats (6,7)
> Select * from sys.dm_io_virtual_file_stats (6,8)
> Select * from sys.dm_io_virtual_file_stats (6,9)
> Select * from sys.dm_io_virtual_file_stats (6,10)
> gives results like :-
> 67170853985015624218246512844829457222526431245540454412438340
> 307010565449074954240x0000000000000954
> It worries me that when the process is on the PAGEIOLATCH the wait
> can be over 1000. Is it normal for the wait to be this long and what
> would be the best way to prove one way or another if the configuration
> of the san is causing this kind of performance?
> Thanks for any suggestions in advance
> Ian.
>
|||ps. The perf mon stats we are seeing are as follows :-
Avg Disk Read/Sec are between 0.06 and 0.3 on the select partion when
the routine starts up. which i think must be a little slow.
Disk Read/Sec is around 170
|||I think the SAN guy said it was a single 2gb card on the san and that
all servers that accessed the same san also used 2gb cards so he knows
this could be a bottleneck, the entire company uses this san for just
about all server ... i would estimate there must be about 6 MIS type
servers using this SAN 3 live and 3 test plus a number of smaller TP
type systems.
I'll run a few tests on the throughput and let you know
Ian
|||Ok, just done a quick table scan on a large table and the stats were
as follows :-
avg disk bytes/sec approx 76,000 which is on 76k, not exactly the
200mb a sec you had said.
Avg disk sec/read 0.6
I take it these figures aren't too good. Any idea what i can check to
see why they look so bad?
Ian.
|||stats on the tablescan are as follows :-
Avg. Disk Bytes/Read 76,000 on average which = 76k which isn't
anything near your 200mb/sec
Avg. Disk sec/Read = 0.6
Any ideas how i can narrow down exactly why the throughput is so bad?
Thanks
Ian.
|||> the former will not be good. Ideally, the latter should be larger than 64K
> and the fomer should be close to 200MB/sec.
Just noticed a mistake here. What I meant to say is that the I/O block size
(i.e. Avg Disk Bytes/Read) shoud be larger than 64K for reporting queries,
and the throughput (i.e. Avg. Read Bytes/sec) should be close to ~200MB/sec
in the ideal situation (given your setup).
Linchi
"Linchi Shea" wrote:
[vbcol=seagreen]
> The SAN setup is not necessarily a problem. Spreading the I/Os over as many
> spindles as possible is in general a good approach.
> There are three possible causes: (1) your SQL query is not efficient (e.g.
> bad plan), (2) you are I/O throughput limited, and (3) the performance is
> killed by the I/O latency problem. Point 3 is unlikely as relevant as Point 2
> in this case because you query appears to be dealing a large data set.
> I'd first check if an inefficient parallel plan is being used. Assume that
> your I/O subsystem can do ~200MB/sec, it shouldbe able to pull in over a
> terabyte of data in 2 hours unless the query plan is such that it results in
> smaller I/Os or not stressing I/O at all. A bad parallelism plan can do that
> to you.
> You can check several perfmon counters to get a feel whether the I/O
> subsystem is saturated, in particular: Avg. Disk Bytes/Read and Avg. Disk
> sec/Read. The former tells you how much throughput you are using and the
> latter tells you how large is each read. To efficiently process
> reporting-type of queries, the latter should be relatively large. Otherwise,
> the former will not be good. Ideally, the latter should be larger than 64K
> and the fomer should be close to 200MB/sec.
> To be sure about what kind of throughput you can get from the drive
> presented from your SAN, you should run some tests. If you have a chance, try
> a simple table scan on a large and wide table to see how many MBs/sec you
> achieve.
> BTW, are you using a sinle 2Gb card or two load-balanced 1Gb cards?
> Linchi
> "ianwr" wrote:
|||Just to make sure I'm raeding the right numbers. You said 76K/sec and
76K/read. These two numbers don't jive (unless the I/O subsystem is so
horribly bad). So which is which?
Assume that it's 76K/read and the thoughput in terms of bytes/sec is also in
KB's not in MB's. A latency of 0.6 second with a I/O block size of ~76K and
such a low throughput is bad. (BTW, I didn't mean to suggest 200MB/sec for
Avg. Disk Bytes/Read. Rather, I meant to say Avg. Read Bytes/sec,, i.e. the
throughput not the I/O block size. That was a typo in my previous reply).
One way to be 100% sure whether you got a raw deal on the storage is to run
some more specific I/O benchmark tests with IOMeter or sqlio.exe since that
would take SQL Server out of the picture and just focus on the performance of
the disk I/O subsystem. If the numbers are bad from IOMeter or sqlio.exe, you
can use those numbers to confront your SAN folks, and they should not have
any excuses.
Linchi
"ianwr" wrote:

> stats on the tablescan are as follows :-
> Avg. Disk Bytes/Read 76,000 on average which = 76k which isn't
> anything near your 200mb/sec
> Avg. Disk sec/Read = 0.6
> Any ideas how i can narrow down exactly why the throughput is so bad?
> Thanks
> Ian.
>
|||Thanks, will try again on monday then and see where the numbers are.
Will post back on the problem next week, but thanks for the responses
so far anyway.
Ian.
|||Holy crap! 600ms disk sec/read is absolutely horrible! This number on a
properly tuned I/O subsystem sould be 2 ORDERS OF MAGNITUDE lower! The SAN
is clearly overburdened and you will never get adequate performance with
that kind of latency.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:8f28f2c6-179f-4324-8b1b-1ee378b3c157@.y43g2000hsy.googlegroups.com...
> stats on the tablescan are as follows :-
> Avg. Disk Bytes/Read 76,000 on average which = 76k which isn't
> anything near your 200mb/sec
> Avg. Disk sec/Read = 0.6
> Any ideas how i can narrow down exactly why the throughput is so bad?
> Thanks
> Ian.
sql

question regarding slow performance an PAGEIOLATCH_SH locks on sql2005

Hi,

I wondered in anyone can help with the following problem that i'm
experiencing, i'll try to provide as much info as possible and any
suggestions would be appreciated.

I have just started at an organsiation and there seems to be slow
performance maybe on the san on a 64bit itanium dual core machine. 4
CPUs are being showed to sql server, it also has 16gb of RAM. I'll
start with the configuration of the SAN.

After speaking to the SAN guy, rather than carve the SAN up into
different area's for san Logs/Data etc they have gone for the approach
of spreading a Vdisk across as many spindles as possible (All 145 of
them). So the area that is presented to the SQL Server according the
the SAN guys is a vraid 5 stripe made up of all 145 disks which are
all 72gb fibre-channel disks.

This storage is not just made available to sql server but also made
available to other apps as well that need storage. Having read the
manufactres best practice on setting this up there is a valid argument
for doing this.

The bandwidth from the SAN is 2Gb fibre, with each computer that uses
the SAN having 2Gb fibre cards.
Clearly, that could act as a bottle-neck. But, there's nothing that
can be done about it according to the SAN guy.

Needless to say, any changes on the SAN are pretty much going to be
out of the question as far as he's concerned but i think performance
isn't that good for the type of box they have and the SAN its attached
to.

The 2nd thing i'll explain is the setup of the database in question,
firstly whoever set it up split the database into 16 different file of
4 filegroups so the table that i'm selecting to is in one filegroup
split over 4 files and the the table selecting from is in another
filegroup made up of another 4 files. These are placed on the same
physical disk made up of the SAN LUN with 145 spindles.

Anyway when i do a select from a sales table which has various group
bys and then insert the results into a blank table with no indexes it
can take over 2hours for 200k rows which i find very slow.

When i look at the sysprocesses table i am getting various waits as
follows :-

72427200x0042900PAGEIOLATCH_SH 6:9:2192094
72427200x00690SLEEP_TASK
72427200x00000SOS_SCHEDULER_YIELD

The process seams to be going inbetween a PAGEIOLATCH and
SOS_SCHEDULER_YIELD a few times per second.

Running the following to get io stalls gives the following :-

Select * from sys.dm_io_virtual_file_stats (6,7)
Select * from sys.dm_io_virtual_file_stats (6,8)
Select * from sys.dm_io_virtual_file_stats (6,9)
Select * from sys.dm_io_virtual_file_stats (6,10)

gives results like :-

67170853985015624218246512844829457222526431245540454412438340
307010565449074954240x0000000000000954

It worries me that when the process is on the PAGEIOLATCH the wait
can be over 1000. Is it normal for the wait to be this long and what
would be the best way to prove one way or another if the configuration
of the san is causing this kind of performance?

Thanks for any suggestions in advance

Ian.ps. The perf mon stats we are seeing are as follows :-

Avg Disk Read/Sec are between 0.06 and 0.3 on the select partion when
the routine starts up. which i think must be a little slow.
Disk Read/Sec is around 170

Question regarding backup of 2005 database to 2000.

I know it's not possible to backup a database in SQL2005, and restore to 2000. But, I've been asked if there are any 3rd party tools which can do this - such as SQL Litespeed - but I can't see anything regarding this on their website.

Does anyone know if litespeed or Redgate SQL Backup can restore to 2000 from 2005 ?

Thanks in advance,

Hi

You can do the following:

1. In SQL Management studio, generate the database script, you can specify that script be generated for SQL 2000.

2. Create the database and tables using the script.

3. DTS or SSIS the data from SQL 2005 tables to SQL 2000 tables.

4. Create the table contraints like indexes and foreign keys using the script generated in step 1.

5. Create rest of the objects.

hope that helps.

Jag

|||No. Every tool I have seen does a TSQL "BACKUP DATABASE" command to their own virtual file/drive.

The structure has changed and views, etc which are not compatible with 2000. There is no way to "backup" a 2005 and restore to 2000.

As mentioned you can create a script and output all the data and import it back into 2000.

question on SELECT 1 and Profiler

I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
and I see a lot of SELECT 1 statements that are running longer in sql
2005 than in sql 2000. what are these select 1 statements?
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1164034938.054630.206620@.k70g2000cwa.googlegr oups.com...
> I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
> and I see a lot of SELECT 1 statements that are running longer in sql
> 2005 than in sql 2000. what are these select 1 statements?
>
SELECT 1
Is a query that returns a single row and a single column containing the
value 1. It's extremely cheap, and probably being used as kind of "ping"
from some application. It's such a cheap query that its cost is probably
just background noise.
David
|||How much longer? 1,000 times longer? SQL Server 2005's profiler can
track time in Miliseconds or Microseconds. I believe the default is
microseconds. If something took 1 ms (would not have shown that level
of detail, but just for an example.. In 2000 I believe the lowest level
of detail visible was about 10 or 12 ms, one of the reasons the
microseconds are used, to provide much more detail over the life of a
query and it's trending time) in 2005 profiler under microseconds it
would be 1,000 (still 1 milisecond, however).
David Browne wrote:
> "Derek" <gepetto_2000@.yahoo.com> wrote in message
> news:1164034938.054630.206620@.k70g2000cwa.googlegr oups.com...
> SELECT 1
> Is a query that returns a single row and a single column containing the
> value 1. It's extremely cheap, and probably being used as kind of "ping"
> from some application. It's such a cheap query that its cost is probably
> just background noise.
> David
|||First of all, the SELECT 1 query is used by Connection Pools to maintain
those connections as "live" so the pool manager does not close them. Only
the base connections should be executing them; otherwise, the pool would
never shrink.
Next, when you say 1,000 times longer, I'm not sure what you mean. On a
SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
execute the same script on SS2K5. What are the average per execution
duration and the overall duration for each of these installations?
Now, there can be environmental factors that you should be careful about,
and I would prefer to run these against each instance, but on the same
hardware, but they should still be roughly the same. There's just not a
whole lot of improvements a system could make on such a simple query.
Sincerely,
Anthony Thomas

"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164074119.932839.211510@.j44g2000cwa.googlegr oups.com...[vbcol=seagreen]
> How much longer? 1,000 times longer? SQL Server 2005's profiler can
> track time in Miliseconds or Microseconds. I believe the default is
> microseconds. If something took 1 ms (would not have shown that level
> of detail, but just for an example.. In 2000 I believe the lowest level
> of detail visible was about 10 or 12 ms, one of the reasons the
> microseconds are used, to provide much more detail over the life of a
> query and it's trending time) in 2005 profiler under microseconds it
> would be 1,000 (still 1 milisecond, however).
>
> David Browne wrote:
driver[vbcol=seagreen]
"ping"[vbcol=seagreen]
probably
>
|||Anthony,
I am not the OP. I did not discuss what the SELECT 1 was. The original
poster indicated that when using profiler, a SELECT 1 query is taking a
lot (never indicated by what factor) longer to run. (Presumably when
looking at Duration in Profiler)
My reply indicated that in SQL Server 2005 you can view your Duration
event in Micro or Miliseconds. In SQL Server 2000 the duration shows
only in miliseconds. I was suggesting that the default behavior (which,
I believe is to view duration in MICROseconds) is probably what is
making the OP's SELECT 1 query appear to run a lot longer in SQL Server
2005. A microsecond is 1,000 times longer than a milisecond.
Sorry that wasn't clear.
Anthony Thomas wrote:[vbcol=seagreen]
> First of all, the SELECT 1 query is used by Connection Pools to maintain
> those connections as "live" so the pool manager does not close them. Only
> the base connections should be executing them; otherwise, the pool would
> never shrink.
> Next, when you say 1,000 times longer, I'm not sure what you mean. On a
> SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
> execute the same script on SS2K5. What are the average per execution
> duration and the overall duration for each of these installations?
> Now, there can be environmental factors that you should be careful about,
> and I would prefer to run these against each instance, but on the same
> hardware, but they should still be roughly the same. There's just not a
> whole lot of improvements a system could make on such a simple query.
> Sincerely,
>
> Anthony Thomas
>
> --
> "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> news:1164074119.932839.211510@.j44g2000cwa.googlegr oups.com...
> driver
> "ping"
> probably
|||Yes, I think your analysis is correct. But, when in doubt, test it out. If
anything, just out of curiosity.
Anthony Thomas

"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164129078.760070.81590@.j44g2000cwa.googlegro ups.com...[vbcol=seagreen]
> Anthony,
> I am not the OP. I did not discuss what the SELECT 1 was. The original
> poster indicated that when using profiler, a SELECT 1 query is taking a
> lot (never indicated by what factor) longer to run. (Presumably when
> looking at Duration in Profiler)
> My reply indicated that in SQL Server 2005 you can view your Duration
> event in Micro or Miliseconds. In SQL Server 2000 the duration shows
> only in miliseconds. I was suggesting that the default behavior (which,
> I believe is to view duration in MICROseconds) is probably what is
> making the OP's SELECT 1 query appear to run a lot longer in SQL Server
> 2005. A microsecond is 1,000 times longer than a milisecond.
> Sorry that wasn't clear.
>
> Anthony Thomas wrote:
Only[vbcol=seagreen]
about,[vbcol=seagreen]
level[vbcol=seagreen]
sql[vbcol=seagreen]
the
>

question on SELECT 1 and Profiler

I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
and I see a lot of SELECT 1 statements that are running longer in sql
2005 than in sql 2000. what are these select 1 statements?"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
> and I see a lot of SELECT 1 statements that are running longer in sql
> 2005 than in sql 2000. what are these select 1 statements?
>
SELECT 1
Is a query that returns a single row and a single column containing the
value 1. It's extremely cheap, and probably being used as kind of "ping"
from some application. It's such a cheap query that its cost is probably
just background noise.
David|||How much longer? 1,000 times longer? SQL Server 2005's profiler can
track time in Miliseconds or Microseconds. I believe the default is
microseconds. If something took 1 ms (would not have shown that level
of detail, but just for an example.. In 2000 I believe the lowest level
of detail visible was about 10 or 12 ms, one of the reasons the
microseconds are used, to provide much more detail over the life of a
query and it's trending time) in 2005 profiler under microseconds it
would be 1,000 (still 1 milisecond, however).
David Browne wrote:
> "Derek" <gepetto_2000@.yahoo.com> wrote in message
> news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> SELECT 1
> Is a query that returns a single row and a single column containing the
> value 1. It's extremely cheap, and probably being used as kind of "ping"
> from some application. It's such a cheap query that its cost is probably
> just background noise.
> David|||First of all, the SELECT 1 query is used by Connection Pools to maintain
those connections as "live" so the pool manager does not close them. Only
the base connections should be executing them; otherwise, the pool would
never shrink.
Next, when you say 1,000 times longer, I'm not sure what you mean. On a
SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
execute the same script on SS2K5. What are the average per execution
duration and the overall duration for each of these installations?
Now, there can be environmental factors that you should be careful about,
and I would prefer to run these against each instance, but on the same
hardware, but they should still be roughly the same. There's just not a
whole lot of improvements a system could make on such a simple query.
Sincerely,
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164074119.932839.211510@.j44g2000cwa.googlegroups.com...
> How much longer? 1,000 times longer? SQL Server 2005's profiler can
> track time in Miliseconds or Microseconds. I believe the default is
> microseconds. If something took 1 ms (would not have shown that level
> of detail, but just for an example.. In 2000 I believe the lowest level
> of detail visible was about 10 or 12 ms, one of the reasons the
> microseconds are used, to provide much more detail over the life of a
> query and it's trending time) in 2005 profiler under microseconds it
> would be 1,000 (still 1 milisecond, however).
>
> David Browne wrote:
driver[vbcol=seagreen]
"ping"[vbcol=seagreen]
probably[vbcol=seagreen]
>|||Anthony,
I am not the OP. I did not discuss what the SELECT 1 was. The original
poster indicated that when using profiler, a SELECT 1 query is taking a
lot (never indicated by what factor) longer to run. (Presumably when
looking at Duration in Profiler)
My reply indicated that in SQL Server 2005 you can view your Duration
event in Micro or Miliseconds. In SQL Server 2000 the duration shows
only in miliseconds. I was suggesting that the default behavior (which,
I believe is to view duration in MICROseconds) is probably what is
making the OP's SELECT 1 query appear to run a lot longer in SQL Server
2005. A microsecond is 1,000 times longer than a milisecond.
Sorry that wasn't clear.
Anthony Thomas wrote:[vbcol=seagreen]
> First of all, the SELECT 1 query is used by Connection Pools to maintain
> those connections as "live" so the pool manager does not close them. Only
> the base connections should be executing them; otherwise, the pool would
> never shrink.
> Next, when you say 1,000 times longer, I'm not sure what you mean. On a
> SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
> execute the same script on SS2K5. What are the average per execution
> duration and the overall duration for each of these installations?
> Now, there can be environmental factors that you should be careful about,
> and I would prefer to run these against each instance, but on the same
> hardware, but they should still be roughly the same. There's just not a
> whole lot of improvements a system could make on such a simple query.
> Sincerely,
>
> Anthony Thomas
>
> --
> "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> news:1164074119.932839.211510@.j44g2000cwa.googlegroups.com...
> driver
> "ping"
> probably|||Yes, I think your analysis is correct. But, when in doubt, test it out. If
anything, just out of curiosity.
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164129078.760070.81590@.j44g2000cwa.googlegroups.com...
> Anthony,
> I am not the OP. I did not discuss what the SELECT 1 was. The original
> poster indicated that when using profiler, a SELECT 1 query is taking a
> lot (never indicated by what factor) longer to run. (Presumably when
> looking at Duration in Profiler)
> My reply indicated that in SQL Server 2005 you can view your Duration
> event in Micro or Miliseconds. In SQL Server 2000 the duration shows
> only in miliseconds. I was suggesting that the default behavior (which,
> I believe is to view duration in MICROseconds) is probably what is
> making the OP's SELECT 1 query appear to run a lot longer in SQL Server
> 2005. A microsecond is 1,000 times longer than a milisecond.
> Sorry that wasn't clear.
>
> Anthony Thomas wrote:
Only[vbcol=seagreen]
about,[vbcol=seagreen]
level[vbcol=seagreen]
sql[vbcol=seagreen]
the[vbcol=seagreen]
>

Question on Report Builder/Designer

I have Visual Studio 2005 Team Developer. I don't need SQL2005 since I have SQL2000. How do I get Report Designer/Builder for VS2005? I would like to use the Report Designer/Builder to build reports for Windows applications. Do I HAVE to buy SQL 2005?

Thanks.

Reporting services is an add-on for SQL 2000, but it is available. You can download a 120-day evaluation version here:

http://www.microsoft.com/downloads/details.aspx?familyid=BA517C01-2E2F-4BC7-84AF-149B7637F807&displaylang=en

|||

I don't want SQL2000 I already have SQL2000. What I want to find is the Report Builder/Designer to use with VB programs within Windows. It is suppose to plug into Visual Studio 2005 which I have but I don't want to spend the money for SQL2005 just to get the plug in. I don't need Report Server just the Report Designer/Builder.

Jim

|||Report Builder is included with Reporting Services. For example, when I load my SQL Server 2005 Reporting Services site and click Build Reports, it launches the Report Builder.|||

It is also an snap-in to Visual Studio. Everything I have read is that you use it to develop reports for Windows Apps, which is what I want to do. I don't use SQL as a database generally. I normally use Access because they are small apps. But I would like to use the Report Builder/Designer to develop these app reports. That is where I'm trying to get to.

If I can get Report Builder/Designer into Visual Studio2005 then I can write reports for Windows Apps which they say you can do and is a replacement for Crystal. Also that you don't need SQL or SSRS to do this.

I just don't know where to get the snap/plug-in.

question on proxy with sql 2005

I am a recent user of sql2005 but have worked with sql2000 for a few years.
I noticed under the sql server agent there is a listing for proxy. I have
used the sql server agent to set up jobs to run packages developed with
Integration services. I was wondering if anyone had a simple explination of
proxys and how/why to set them up.
Thanks.
Paul G
Software engineer.
Paul,
My take on the proxy is if you need to run something from inside SQL that
needs to go outside of SQL you need the proxy account. So if an SQL process
needs to run xp_cmdshell then you need the proxy account. We have cases
where we start inside SQL and go outside to another server that runs a
process inside another SQL server. Here the proxy is needed to go outside
and needs to be defined on the other server and possibly given access to SQL
resources inside the other SQL Server otherwise it will not work.
Clear as mud!
To use xm\p_cmdshell you need to make it available SAC and give permission
to run it and then either create the proxy Under
Server/Properties/Security/Server proxy Account or by using the system sp
sp_xp_cmdshell_proxy_account. They both do the same thing.
HTH
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>I am a recent user of sql2005 but have worked with sql2000 for a few years.
> I noticed under the sql server agent there is a listing for proxy. I have
> used the sql server agent to set up jobs to run packages developed with
> Integration services. I was wondering if anyone had a simple explination
> of
> proxys and how/why to set them up.
> Thanks.
> --
> Paul G
> Software engineer.
|||Hi Chris, thanks for the information. I think it makes sense, will probably
try to set up an example when I have some time. I guess for example if you
wanted to run an Integration Services package on server two and some how
initiate it with a job on server one, the proxy acount might be used? Also
not sure if you have run into this but I have a 2003 web app and when I open
it from vs 2003I get the error message unable to get the project file from
the web server, the two need to map to the same server? thanks.
Paul G
Software engineer.
"Chris Wood" wrote:

> Paul,
> My take on the proxy is if you need to run something from inside SQL that
> needs to go outside of SQL you need the proxy account. So if an SQL process
> needs to run xp_cmdshell then you need the proxy account. We have cases
> where we start inside SQL and go outside to another server that runs a
> process inside another SQL server. Here the proxy is needed to go outside
> and needs to be defined on the other server and possibly given access to SQL
> resources inside the other SQL Server otherwise it will not work.
> Clear as mud!
> To use xm\p_cmdshell you need to make it available SAC and give permission
> to run it and then either create the proxy Under
> Server/Properties/Security/Server proxy Account or by using the system sp
> sp_xp_cmdshell_proxy_account. They both do the same thing.
> HTH
> Chris
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>
>
|||Paul,
THE proxy Account certainly does xp_cmdshell but you can have others and
assign them to do different tasks. Looks at SQL Server/Agent/Proxies. You
can create these using Security/Credentials. This is the way to define a
proxy and what it can use. As I mentioned the xp_cmdshell is defined using
the Server/Properties/Security/Server proxy Account.
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4325CA59-D1D9-4DA9-855B-FC8BF59C0338@.microsoft.com...[vbcol=seagreen]
> Hi Chris, thanks for the information. I think it makes sense, will
> probably
> try to set up an example when I have some time. I guess for example if
> you
> wanted to run an Integration Services package on server two and some how
> initiate it with a job on server one, the proxy acount might be used?
> Also
> not sure if you have run into this but I have a 2003 web app and when I
> open
> it from vs 2003I get the error message unable to get the project file from
> the web server, the two need to map to the same server? thanks.
> --
> Paul G
> Software engineer.
>
> "Chris Wood" wrote:

question on proxy with sql 2005

I am a recent user of sql2005 but have worked with sql2000 for a few years.
I noticed under the sql server agent there is a listing for proxy. I have
used the sql server agent to set up jobs to run packages developed with
Integration services. I was wondering if anyone had a simple explination of
proxys and how/why to set them up.
Thanks.
--
Paul G
Software engineer.Paul,
My take on the proxy is if you need to run something from inside SQL that
needs to go outside of SQL you need the proxy account. So if an SQL process
needs to run xp_cmdshell then you need the proxy account. We have cases
where we start inside SQL and go outside to another server that runs a
process inside another SQL server. Here the proxy is needed to go outside
and needs to be defined on the other server and possibly given access to SQL
resources inside the other SQL Server otherwise it will not work.
Clear as mud!
To use xm\p_cmdshell you need to make it available SAC and give permission
to run it and then either create the proxy Under
Server/Properties/Security/Server proxy Account or by using the system sp
sp_xp_cmdshell_proxy_account. They both do the same thing.
HTH
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>I am a recent user of sql2005 but have worked with sql2000 for a few years.
> I noticed under the sql server agent there is a listing for proxy. I have
> used the sql server agent to set up jobs to run packages developed with
> Integration services. I was wondering if anyone had a simple explination
> of
> proxys and how/why to set them up.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi Chris, thanks for the information. I think it makes sense, will probably
try to set up an example when I have some time. I guess for example if you
wanted to run an Integration Services package on server two and some how
initiate it with a job on server one, the proxy acount might be used? Also
not sure if you have run into this but I have a 2003 web app and when I open
it from vs 2003I get the error message unable to get the project file from
the web server, the two need to map to the same server? thanks.
--
Paul G
Software engineer.
"Chris Wood" wrote:

> Paul,
> My take on the proxy is if you need to run something from inside SQL that
> needs to go outside of SQL you need the proxy account. So if an SQL proces
s
> needs to run xp_cmdshell then you need the proxy account. We have cases
> where we start inside SQL and go outside to another server that runs a
> process inside another SQL server. Here the proxy is needed to go outside
> and needs to be defined on the other server and possibly given access to S
QL
> resources inside the other SQL Server otherwise it will not work.
> Clear as mud!
> To use xm\p_cmdshell you need to make it available SAC and give permission
> to run it and then either create the proxy Under
> Server/Properties/Security/Server proxy Account or by using the system sp
> sp_xp_cmdshell_proxy_account. They both do the same thing.
> HTH
> Chris
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>
>|||Paul,
THE proxy Account certainly does xp_cmdshell but you can have others and
assign them to do different tasks. Looks at SQL Server/Agent/Proxies. You
can create these using Security/Credentials. This is the way to define a
proxy and what it can use. As I mentioned the xp_cmdshell is defined using
the Server/Properties/Security/Server proxy Account.
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4325CA59-D1D9-4DA9-855B-FC8BF59C0338@.microsoft.com...[vbcol=seagreen]
> Hi Chris, thanks for the information. I think it makes sense, will
> probably
> try to set up an example when I have some time. I guess for example if
> you
> wanted to run an Integration Services package on server two and some how
> initiate it with a job on server one, the proxy acount might be used?
> Also
> not sure if you have run into this but I have a 2003 web app and when I
> open
> it from vs 2003I get the error message unable to get the project file from
> the web server, the two need to map to the same server? thanks.
> --
> Paul G
> Software engineer.
>
> "Chris Wood" wrote:
>

question on proxy with sql 2005

I am a recent user of sql2005 but have worked with sql2000 for a few years.
I noticed under the sql server agent there is a listing for proxy. I have
used the sql server agent to set up jobs to run packages developed with
Integration services. I was wondering if anyone had a simple explination of
proxys and how/why to set them up.
Thanks.
--
Paul G
Software engineer.Paul,
My take on the proxy is if you need to run something from inside SQL that
needs to go outside of SQL you need the proxy account. So if an SQL process
needs to run xp_cmdshell then you need the proxy account. We have cases
where we start inside SQL and go outside to another server that runs a
process inside another SQL server. Here the proxy is needed to go outside
and needs to be defined on the other server and possibly given access to SQL
resources inside the other SQL Server otherwise it will not work.
Clear as mud!
To use xm\p_cmdshell you need to make it available SAC and give permission
to run it and then either create the proxy Under
Server/Properties/Security/Server proxy Account or by using the system sp
sp_xp_cmdshell_proxy_account. They both do the same thing.
HTH
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>I am a recent user of sql2005 but have worked with sql2000 for a few years.
> I noticed under the sql server agent there is a listing for proxy. I have
> used the sql server agent to set up jobs to run packages developed with
> Integration services. I was wondering if anyone had a simple explination
> of
> proxys and how/why to set them up.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi Chris, thanks for the information. I think it makes sense, will probably
try to set up an example when I have some time. I guess for example if you
wanted to run an Integration Services package on server two and some how
initiate it with a job on server one, the proxy acount might be used? Also
not sure if you have run into this but I have a 2003 web app and when I open
it from vs 2003I get the error message unable to get the project file from
the web server, the two need to map to the same server? thanks.
--
Paul G
Software engineer.
"Chris Wood" wrote:
> Paul,
> My take on the proxy is if you need to run something from inside SQL that
> needs to go outside of SQL you need the proxy account. So if an SQL process
> needs to run xp_cmdshell then you need the proxy account. We have cases
> where we start inside SQL and go outside to another server that runs a
> process inside another SQL server. Here the proxy is needed to go outside
> and needs to be defined on the other server and possibly given access to SQL
> resources inside the other SQL Server otherwise it will not work.
> Clear as mud!
> To use xm\p_cmdshell you need to make it available SAC and give permission
> to run it and then either create the proxy Under
> Server/Properties/Security/Server proxy Account or by using the system sp
> sp_xp_cmdshell_proxy_account. They both do the same thing.
> HTH
> Chris
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
> >I am a recent user of sql2005 but have worked with sql2000 for a few years.
> > I noticed under the sql server agent there is a listing for proxy. I have
> > used the sql server agent to set up jobs to run packages developed with
> > Integration services. I was wondering if anyone had a simple explination
> > of
> > proxys and how/why to set them up.
> > Thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||Paul,
THE proxy Account certainly does xp_cmdshell but you can have others and
assign them to do different tasks. Looks at SQL Server/Agent/Proxies. You
can create these using Security/Credentials. This is the way to define a
proxy and what it can use. As I mentioned the xp_cmdshell is defined using
the Server/Properties/Security/Server proxy Account.
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4325CA59-D1D9-4DA9-855B-FC8BF59C0338@.microsoft.com...
> Hi Chris, thanks for the information. I think it makes sense, will
> probably
> try to set up an example when I have some time. I guess for example if
> you
> wanted to run an Integration Services package on server two and some how
> initiate it with a job on server one, the proxy acount might be used?
> Also
> not sure if you have run into this but I have a 2003 web app and when I
> open
> it from vs 2003I get the error message unable to get the project file from
> the web server, the two need to map to the same server? thanks.
> --
> Paul G
> Software engineer.
>
> "Chris Wood" wrote:
>> Paul,
>> My take on the proxy is if you need to run something from inside SQL that
>> needs to go outside of SQL you need the proxy account. So if an SQL
>> process
>> needs to run xp_cmdshell then you need the proxy account. We have cases
>> where we start inside SQL and go outside to another server that runs a
>> process inside another SQL server. Here the proxy is needed to go outside
>> and needs to be defined on the other server and possibly given access to
>> SQL
>> resources inside the other SQL Server otherwise it will not work.
>> Clear as mud!
>> To use xm\p_cmdshell you need to make it available SAC and give
>> permission
>> to run it and then either create the proxy Under
>> Server/Properties/Security/Server proxy Account or by using the system sp
>> sp_xp_cmdshell_proxy_account. They both do the same thing.
>> HTH
>> Chris
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>> >I am a recent user of sql2005 but have worked with sql2000 for a few
>> >years.
>> > I noticed under the sql server agent there is a listing for proxy. I
>> > have
>> > used the sql server agent to set up jobs to run packages developed with
>> > Integration services. I was wondering if anyone had a simple
>> > explination
>> > of
>> > proxys and how/why to set them up.
>> > Thanks.
>> > --
>> > Paul G
>> > Software engineer.
>>