Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts

Wednesday, March 28, 2012

Latest TPC bencmarks killing SQl Server

After Microsoft's doing so well in the TPC raw performance bechmark for so
long, Oracle appears to have finally leapfrogged SQL Server on both
Clustered and Non-Clustered configurations. In fact, not only has Oracle
leapfrogged them but literally blew SQL Server away.
What is REALLY interesting about this is the fact that the number-one
performance for the clustered configuration is both considerably higher than
Microsoft and WITH BETTER PRICE PERFORMANCE, which has always been
Microsoft's strength.
This appears to be a function of using an Open Source OS (Linux).
I still generally prefer SQL Server over Oracle (easier to tune and
maintain) but have been troubled by Microsoft's trend AWAY from commodity
pricing (that they pioneered at the high end) at the same time Enterprise
providers have been trending toward it. I'm wondering if this latest
development might single an inflection point where Microsoft begins to
rethink ever-upward pricing and once again return to a more aggresive
position in this area.It's a constant battle to keep the numbers up. If MS posts something Oracle
or DB2 will better it and then MS will have their turn. It's almost
pointless now that the numbers are so far out of what systems need now a
days anyway. The hardware has caught up with the rest of the world in this
respect and it's only getting better.
--
Andrew J. Kelly
SQL Server MVP
"nospam" <nfr@.nospam.com> wrote in message
news:%23%236UwKmzDHA.2000@.TK2MSFTNGP11.phx.gbl...
> After Microsoft's doing so well in the TPC raw performance bechmark for so
> long, Oracle appears to have finally leapfrogged SQL Server on both
> Clustered and Non-Clustered configurations. In fact, not only has Oracle
> leapfrogged them but literally blew SQL Server away.
> What is REALLY interesting about this is the fact that the number-one
> performance for the clustered configuration is both considerably higher
than
> Microsoft and WITH BETTER PRICE PERFORMANCE, which has always been
> Microsoft's strength.
> This appears to be a function of using an Open Source OS (Linux).
> I still generally prefer SQL Server over Oracle (easier to tune and
> maintain) but have been troubled by Microsoft's trend AWAY from commodity
> pricing (that they pioneered at the high end) at the same time Enterprise
> providers have been trending toward it. I'm wondering if this latest
> development might single an inflection point where Microsoft begins to
> rethink ever-upward pricing and once again return to a more aggresive
> position in this area.
>|||As far as non-clustered goes, something you need to take into account is
that the top Oracle NC result is on a box that has 1tb of main memory. The
top MS SQL NC result is on virtually the same box, but only has half the
memory. It's reasonable to assume that the top MS SQL NC result is probably
similar to the top Oracle NC result if they were run on the same hardware.
As far as the use of Linux is concerned, the O/S cost is only a small
fraction of the overall cost of that benchmark. Download the full disclosure
statement & I'd suggest you'll see that the cost achieved by Oracle is due
to commodity pricing of grid components & the cost saving from use of the
Linux O/S is a relatively small contributor to the overall cost saving on
Oracle's C result.
The last thing to take into account is that Oracle's result is based on 10g.
It will be interesting to see what Yukon can do.
Regards,
Greg Linwood
SQL Server MVP
"nospam" <nfr@.nospam.com> wrote in message
news:%23%236UwKmzDHA.2000@.TK2MSFTNGP11.phx.gbl...
> After Microsoft's doing so well in the TPC raw performance bechmark for so
> long, Oracle appears to have finally leapfrogged SQL Server on both
> Clustered and Non-Clustered configurations. In fact, not only has Oracle
> leapfrogged them but literally blew SQL Server away.
> What is REALLY interesting about this is the fact that the number-one
> performance for the clustered configuration is both considerably higher
than
> Microsoft and WITH BETTER PRICE PERFORMANCE, which has always been
> Microsoft's strength.
> This appears to be a function of using an Open Source OS (Linux).
> I still generally prefer SQL Server over Oracle (easier to tune and
> maintain) but have been troubled by Microsoft's trend AWAY from commodity
> pricing (that they pioneered at the high end) at the same time Enterprise
> providers have been trending toward it. I'm wondering if this latest
> development might single an inflection point where Microsoft begins to
> rethink ever-upward pricing and once again return to a more aggresive
> position in this area.
>|||Agree with all your points.
I would actually like to see one of these top-end benchmarks at least run on
as identical hardware as possible, such as the SuperDome, with the variants
being the database, TP Monitor, and OS. That is an integrated MS solution
vs. a non-MS "best-of-breed" approach.
IOW, I'd like to see the HW held constant and look for performance advantage
in software platform configurations.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:ebPCK8mzDHA.1524@.TK2MSFTNGP10.phx.gbl...
> As far as non-clustered goes, something you need to take into account is
> that the top Oracle NC result is on a box that has 1tb of main memory. The
> top MS SQL NC result is on virtually the same box, but only has half the
> memory. It's reasonable to assume that the top MS SQL NC result is
probably
> similar to the top Oracle NC result if they were run on the same hardware.
> As far as the use of Linux is concerned, the O/S cost is only a small
> fraction of the overall cost of that benchmark. Download the full
disclosure
> statement & I'd suggest you'll see that the cost achieved by Oracle is due
> to commodity pricing of grid components & the cost saving from use of the
> Linux O/S is a relatively small contributor to the overall cost saving on
> Oracle's C result.
> The last thing to take into account is that Oracle's result is based on
10g.
> It will be interesting to see what Yukon can do.
> Regards,
> Greg Linwood
> SQL Server MVP
> "nospam" <nfr@.nospam.com> wrote in message
> news:%23%236UwKmzDHA.2000@.TK2MSFTNGP11.phx.gbl...
> > After Microsoft's doing so well in the TPC raw performance bechmark for
so
> > long, Oracle appears to have finally leapfrogged SQL Server on both
> > Clustered and Non-Clustered configurations. In fact, not only has Oracle
> > leapfrogged them but literally blew SQL Server away.
> >
> > What is REALLY interesting about this is the fact that the number-one
> > performance for the clustered configuration is both considerably higher
> than
> > Microsoft and WITH BETTER PRICE PERFORMANCE, which has always been
> > Microsoft's strength.
> >
> > This appears to be a function of using an Open Source OS (Linux).
> >
> > I still generally prefer SQL Server over Oracle (easier to tune and
> > maintain) but have been troubled by Microsoft's trend AWAY from
commodity
> > pricing (that they pioneered at the high end) at the same time
Enterprise
> > providers have been trending toward it. I'm wondering if this latest
> > development might single an inflection point where Microsoft begins to
> > rethink ever-upward pricing and once again return to a more aggresive
> > position in this area.
> >
> >
>|||You're not likely to see hardware held constant b/c these benchmarks are
usually released by hardware vendors - they'll always want to show off how
fast their latest "babies" are. (c:
Regards,
Greg Linwood
SQL Server MVP
"nospam" <nfr@.nospam.com> wrote in message
news:%23D$cbQnzDHA.3416@.tk2msftngp13.phx.gbl...
> Agree with all your points.
> I would actually like to see one of these top-end benchmarks at least run
on
> as identical hardware as possible, such as the SuperDome, with the
variants
> being the database, TP Monitor, and OS. That is an integrated MS solution
> vs. a non-MS "best-of-breed" approach.
> IOW, I'd like to see the HW held constant and look for performance
advantage
> in software platform configurations.
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:ebPCK8mzDHA.1524@.TK2MSFTNGP10.phx.gbl...
> > As far as non-clustered goes, something you need to take into account is
> > that the top Oracle NC result is on a box that has 1tb of main memory.
The
> > top MS SQL NC result is on virtually the same box, but only has half the
> > memory. It's reasonable to assume that the top MS SQL NC result is
> probably
> > similar to the top Oracle NC result if they were run on the same
hardware.
> >
> > As far as the use of Linux is concerned, the O/S cost is only a small
> > fraction of the overall cost of that benchmark. Download the full
> disclosure
> > statement & I'd suggest you'll see that the cost achieved by Oracle is
due
> > to commodity pricing of grid components & the cost saving from use of
the
> > Linux O/S is a relatively small contributor to the overall cost saving
on
> > Oracle's C result.
> >
> > The last thing to take into account is that Oracle's result is based on
> 10g.
> > It will be interesting to see what Yukon can do.
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "nospam" <nfr@.nospam.com> wrote in message
> > news:%23%236UwKmzDHA.2000@.TK2MSFTNGP11.phx.gbl...
> > > After Microsoft's doing so well in the TPC raw performance bechmark
for
> so
> > > long, Oracle appears to have finally leapfrogged SQL Server on both
> > > Clustered and Non-Clustered configurations. In fact, not only has
Oracle
> > > leapfrogged them but literally blew SQL Server away.
> > >
> > > What is REALLY interesting about this is the fact that the number-one
> > > performance for the clustered configuration is both considerably
higher
> > than
> > > Microsoft and WITH BETTER PRICE PERFORMANCE, which has always been
> > > Microsoft's strength.
> > >
> > > This appears to be a function of using an Open Source OS (Linux).
> > >
> > > I still generally prefer SQL Server over Oracle (easier to tune and
> > > maintain) but have been troubled by Microsoft's trend AWAY from
> commodity
> > > pricing (that they pioneered at the high end) at the same time
> Enterprise
> > > providers have been trending toward it. I'm wondering if this latest
> > > development might single an inflection point where Microsoft begins to
> > > rethink ever-upward pricing and once again return to a more aggresive
> > > position in this area.
> > >
> > >
> >
> >
>|||3 out of the top 4 benchmark's are on the HP Integrity Superdome, which
theoretically could have been configured identically. The most recent number
1 slot goes to the HP Integrity rx5670, which was a clustered solution.
It's intereesting there is not a single Java-based TP-monitor in the bunch.
It appears the battle is between BEA Tuxedo and COM+.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23ep$xYnzDHA.2160@.TK2MSFTNGP12.phx.gbl...
> You're not likely to see hardware held constant b/c these benchmarks are
> usually released by hardware vendors - they'll always want to show off how
> fast their latest "babies" are. (c:
> Regards,
> Greg Linwood
> SQL Server MVP
> "nospam" <nfr@.nospam.com> wrote in message
> news:%23D$cbQnzDHA.3416@.tk2msftngp13.phx.gbl...
> > Agree with all your points.
> >
> > I would actually like to see one of these top-end benchmarks at least
run
> on
> > as identical hardware as possible, such as the SuperDome, with the
> variants
> > being the database, TP Monitor, and OS. That is an integrated MS
solution
> > vs. a non-MS "best-of-breed" approach.
> >
> > IOW, I'd like to see the HW held constant and look for performance
> advantage
> > in software platform configurations.
> >
> > "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> > news:ebPCK8mzDHA.1524@.TK2MSFTNGP10.phx.gbl...
> > > As far as non-clustered goes, something you need to take into account
is
> > > that the top Oracle NC result is on a box that has 1tb of main memory.
> The
> > > top MS SQL NC result is on virtually the same box, but only has half
the
> > > memory. It's reasonable to assume that the top MS SQL NC result is
> > probably
> > > similar to the top Oracle NC result if they were run on the same
> hardware.
> > >
> > > As far as the use of Linux is concerned, the O/S cost is only a small
> > > fraction of the overall cost of that benchmark. Download the full
> > disclosure
> > > statement & I'd suggest you'll see that the cost achieved by Oracle is
> due
> > > to commodity pricing of grid components & the cost saving from use of
> the
> > > Linux O/S is a relatively small contributor to the overall cost saving
> on
> > > Oracle's C result.
> > >
> > > The last thing to take into account is that Oracle's result is based
on
> > 10g.
> > > It will be interesting to see what Yukon can do.
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "nospam" <nfr@.nospam.com> wrote in message
> > > news:%23%236UwKmzDHA.2000@.TK2MSFTNGP11.phx.gbl...
> > > > After Microsoft's doing so well in the TPC raw performance bechmark
> for
> > so
> > > > long, Oracle appears to have finally leapfrogged SQL Server on both
> > > > Clustered and Non-Clustered configurations. In fact, not only has
> Oracle
> > > > leapfrogged them but literally blew SQL Server away.
> > > >
> > > > What is REALLY interesting about this is the fact that the
number-one
> > > > performance for the clustered configuration is both considerably
> higher
> > > than
> > > > Microsoft and WITH BETTER PRICE PERFORMANCE, which has always been
> > > > Microsoft's strength.
> > > >
> > > > This appears to be a function of using an Open Source OS (Linux).
> > > >
> > > > I still generally prefer SQL Server over Oracle (easier to tune and
> > > > maintain) but have been troubled by Microsoft's trend AWAY from
> > commodity
> > > > pricing (that they pioneered at the high end) at the same time
> > Enterprise
> > > > providers have been trending toward it. I'm wondering if this latest
> > > > development might single an inflection point where Microsoft begins
to
> > > > rethink ever-upward pricing and once again return to a more
aggresive
> > > > position in this area.
> > > >
> > > >
> > >
> > >
> >
> >
>|||Just look at the dates submitted and fact that Oracle has built (how many?) new versions since 1999 and releasing of MS SQL 2000!?sql

Friday, March 9, 2012

Large Truncate Performance

I have a 27 million record table that I need to truncate to free up
storage. Will this have any significat impact on what is a pretty busy 40
+GB database?Hard to tell but truncate is minimally logged and is typically extremely
fast.
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Brad" <me@.privacy.net> wrote in message
news:MPG.1a6384c4ae78ffb898bad6@.news...
> I have a 27 million record table that I need to truncate to free up
> storage. Will this have any significat impact on what is a pretty busy 40
> +GB database?|||Truncate is a fast, nonlogged operation. It should have minimal impact =on performance. With that said, I have tried truncating a table that =large. Be careful with truncate! Once the data is gone, it is gone!
From Books Online:
Deleting All Rows Using TRUNCATE TABLE
The TRUNCATE TABLE statement is a fast, nonlogged method of deleting all =rows in a table. It is almost always faster than a DELETE statement with =no conditions because DELETE logs each row deletion, and TRUNCATE TABLE =logs only the deallocation of whole data pages. TRUNCATE TABLE =immediately frees all the space occupied by that table's data and =indexes. The distribution pages for all indexes are also freed.
As with DELETE, the definition of a table emptied using TRUNCATE TABLE =remains in the database, along with its indexes and other associated =objects. The DROP TABLE statement must be used to drop the definition of =the table.
-- Keith
"Brad" <me@.privacy.net> wrote in message =news:MPG.1a6384c4ae78ffb898bad6@.news...
> I have a 27 million record table that I need to truncate to free up > storage. Will this have any significat impact on what is a pretty =busy 40
> +GB database?|||Just a minor correction. Truncate is minimal logged. This is to ensure that
we would be able to rollback a transaction. Here is a quick demo.
select *
into tmp
from Northwind..Orders
go
begin tran
truncate table tmp
rollback tran
go
select count(*) as 'tmp_cnt_after_truncate' from tmp
go
drop table tmp
go
--
-oj
http://www.rac4sql.net
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OVRBue80DHA.2460@.TK2MSFTNGP10.phx.gbl...
Truncate is a fast, nonlogged operation. It should have minimal impact on
performance. With that said, I have tried truncating a table that large.
Be careful with truncate! Once the data is gone, it is gone!
From Books Online:
Deleting All Rows Using TRUNCATE TABLE
The TRUNCATE TABLE statement is a fast, nonlogged method of deleting all
rows in a table. It is almost always faster than a DELETE statement with no
conditions because DELETE logs each row deletion, and TRUNCATE TABLE logs
only the deallocation of whole data pages. TRUNCATE TABLE immediately frees
all the space occupied by that table's data and indexes. The distribution
pages for all indexes are also freed.
As with DELETE, the definition of a table emptied using TRUNCATE TABLE
remains in the database, along with its indexes and other associated
objects. The DROP TABLE statement must be used to drop the definition of the
table.
Keith
"Brad" <me@.privacy.net> wrote in message
news:MPG.1a6384c4ae78ffb898bad6@.news...
> I have a 27 million record table that I need to truncate to free up
> storage. Will this have any significat impact on what is a pretty busy 40
> +GB database?

Large transaction log causing write performance hit

Hi,
I really would appreciate any help on this. Can a very
large transaction log have a negative impact on write
performance' We were having a problem with VERY slow
writes (30 seconds each) to a database. Reads were very
fast (0 ms). The app is running on a Windows 2000/SQL
2000 Active/Passive cluster. Each server has 2 G of ram
and disk space is absolutely not an issue. The only other
applications that run on the server are Mcaffee (but this
is disabled from the data/log directories).
We noticed that the log file for the affected database was
EXTREMELY large (3G). TONS of leftover disk space for the
log. There's not a whole lot of updates that occur in
this database. The database was in Full recovery mode.
We usually rely on nightly backups due to the sparse
update/inserts/deletes.
After encountering this problem, I failed over the node
(restarted the SQL services, and writes became fast again,
but this was only temporary (lasted a couple hours), which
led me to believe that it could be a memory issue.
Anyway, after that, I made the following changes: 1.
Backed up the database, then the log, shrank the log
(now .99MB) and temporarily put set the recovery mode to
simple. 2. Reduced the max SQL server memory setting to 1
G.
Writes are again very fast. I have seen no problems for a
day and a half under a normal load. It would really help
to know if the large tlog could have actually been the
problem? Or the if the memory settings may have done
the trick and why? Then we can rest assured that the
issue will not crop
up again later!
Thanks,
ChrisThe log could be a problem. Obviously all the uncommitted transactions are
sitting in there, and writing in sequence could be tying up things.
Thinking along those lines, what you need to do is think of a good recovery
model or consider moving the transaction log to a separate disk where there
is nothing on it. I wish I could cite a source better than Transcender
study questions to say that the recommendation I've seen is to put a
transaction log on a mirrored set, but you can find some discussions doing
google searches.
A good recovery model depends on whether you want up to the minute restores
or you can stand to lose everything from the last full /differential backup.
The frequent transaction log backups or truncations will keep the size down
to a manageable one.
If you need up-to-the-minute restores, then consider setting your database
to the full recovery model, and schedule frequent transaction log backups to
disk. Then you can restore additional logs on top of the last full database
restore.
If you can stand to lose everything from the last full backup, then consider
using the simple recovery model (select into/bulkcopy true, trunc log on
chkpt true), which will automatically empty your transaction log at every
checkpoint the database does. Then make sure your full backups and
differential backups are happening at appropriate intervals. Good luck.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Christian Bouche" <cbouche@.gohealthcast.com> wrote in message
news:2a1801c3fc91$500f3fe0$a101280a@.phx.gbl...
> Hi,
> I really would appreciate any help on this. Can a very
> large transaction log have a negative impact on write
> performance' We were having a problem with VERY slow
> writes (30 seconds each) to a database. Reads were very
> fast (0 ms). The app is running on a Windows 2000/SQL
> 2000 Active/Passive cluster. Each server has 2 G of ram
> and disk space is absolutely not an issue. The only other
> applications that run on the server are Mcaffee (but this
> is disabled from the data/log directories).
> We noticed that the log file for the affected database was
> EXTREMELY large (3G). TONS of leftover disk space for the
> log. There's not a whole lot of updates that occur in
> this database. The database was in Full recovery mode.
> We usually rely on nightly backups due to the sparse
> update/inserts/deletes.
> After encountering this problem, I failed over the node
> (restarted the SQL services, and writes became fast again,
> but this was only temporary (lasted a couple hours), which
> led me to believe that it could be a memory issue.
> Anyway, after that, I made the following changes: 1.
> Backed up the database, then the log, shrank the log
> (now .99MB) and temporarily put set the recovery mode to
> simple. 2. Reduced the max SQL server memory setting to 1
> G.
> Writes are again very fast. I have seen no problems for a
> day and a half under a normal load. It would really help
> to know if the large tlog could have actually been the
> problem? Or the if the memory settings may have done
> the trick and why? Then we can rest assured that the
> issue will not crop
> up again later!
> Thanks,
> Chris
>|||Thank you so much for your help.
Chris
>--Original Message--
>The log could be a problem. Obviously all the
uncommitted transactions are
>sitting in there, and writing in sequence could be tying
up things.
>Thinking along those lines, what you need to do is think
of a good recovery
>model or consider moving the transaction log to a
separate disk where there
>is nothing on it. I wish I could cite a source better
than Transcender
>study questions to say that the recommendation I've seen
is to put a
>transaction log on a mirrored set, but you can find some
discussions doing
>google searches.
>A good recovery model depends on whether you want up to
the minute restores
>or you can stand to lose everything from the last
full /differential backup.
>The frequent transaction log backups or truncations will
keep the size down
>to a manageable one.
>If you need up-to-the-minute restores, then consider
setting your database
>to the full recovery model, and schedule frequent
transaction log backups to
>disk. Then you can restore additional logs on top of the
last full database
>restore.
>If you can stand to lose everything from the last full
backup, then consider
>using the simple recovery model (select into/bulkcopy
true, trunc log on
>chkpt true), which will automatically empty your
transaction log at every
>checkpoint the database does. Then make sure your full
backups and
>differential backups are happening at appropriate
intervals. Good luck.
>--
> ****************************************
******************
*********
>Andy S.
>MCSE NT/2000, MCDBA SQL 7/2000
>andymcdba1@.NOMORESPAM.yahoo.com
>Please remove NOMORESPAM before replying.
>Always keep your antivirus and Microsoft software
>up to date with the latest definitions and product
updates.
>Be suspicious of every email attachment, I will never send
>or post anything other than the text of a http:// link nor
>post the link directly to a file for downloading.
>This posting is provided "as is" with no warranties
>and confers no rights.
> ****************************************
******************
*********
>"Christian Bouche" <cbouche@.gohealthcast.com> wrote in
message
>news:2a1801c3fc91$500f3fe0$a101280a@.phx.gbl...
other
this
was
the
again,
which
to 1
for a
help
>
>.
>

Large transaction log causing write performance hit

Hi,
I really would appreciate any help on this. Can a very
large transaction log have a negative impact on write
performance' We were having a problem with VERY slow
writes (30 seconds each) to a database. Reads were very
fast (0 ms). The app is running on a Windows 2000/SQL
2000 Active/Passive cluster. Each server has 2 G of ram
and disk space is absolutely not an issue. The only other
applications that run on the server are Mcaffee (but this
is disabled from the data/log directories).
We noticed that the log file for the affected database was
EXTREMELY large (3G). TONS of leftover disk space for the
log. There's not a whole lot of updates that occur in
this database. The database was in Full recovery mode.
We usually rely on nightly backups due to the sparse
update/inserts/deletes.
After encountering this problem, I failed over the node
(restarted the SQL services, and writes became fast again,
but this was only temporary (lasted a couple hours), which
led me to believe that it could be a memory issue.
Anyway, after that, I made the following changes: 1.
Backed up the database, then the log, shrank the log
(now .99MB) and temporarily put set the recovery mode to
simple. 2. Reduced the max SQL server memory setting to 1
G.
Writes are again very fast. I have seen no problems for a
day and a half under a normal load. It would really help
to know if the large tlog could have actually been the
problem? Or the if the memory settings may have done
the trick and why? Then we can rest assured that the
issue will not crop
up again later!
Thanks,
ChrisThe log could be a problem. Obviously all the uncommitted transactions are
sitting in there, and writing in sequence could be tying up things.
Thinking along those lines, what you need to do is think of a good recovery
model or consider moving the transaction log to a separate disk where there
is nothing on it. I wish I could cite a source better than Transcender
study questions to say that the recommendation I've seen is to put a
transaction log on a mirrored set, but you can find some discussions doing
google searches.
A good recovery model depends on whether you want up to the minute restores
or you can stand to lose everything from the last full /differential backup.
The frequent transaction log backups or truncations will keep the size down
to a manageable one.
If you need up-to-the-minute restores, then consider setting your database
to the full recovery model, and schedule frequent transaction log backups to
disk. Then you can restore additional logs on top of the last full database
restore.
If you can stand to lose everything from the last full backup, then consider
using the simple recovery model (select into/bulkcopy true, trunc log on
chkpt true), which will automatically empty your transaction log at every
checkpoint the database does. Then make sure your full backups and
differential backups are happening at appropriate intervals. Good luck.
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"Christian Bouche" <cbouche@.gohealthcast.com> wrote in message
news:2a1801c3fc91$500f3fe0$a101280a@.phx.gbl...
> Hi,
> I really would appreciate any help on this. Can a very
> large transaction log have a negative impact on write
> performance' We were having a problem with VERY slow
> writes (30 seconds each) to a database. Reads were very
> fast (0 ms). The app is running on a Windows 2000/SQL
> 2000 Active/Passive cluster. Each server has 2 G of ram
> and disk space is absolutely not an issue. The only other
> applications that run on the server are Mcaffee (but this
> is disabled from the data/log directories).
> We noticed that the log file for the affected database was
> EXTREMELY large (3G). TONS of leftover disk space for the
> log. There's not a whole lot of updates that occur in
> this database. The database was in Full recovery mode.
> We usually rely on nightly backups due to the sparse
> update/inserts/deletes.
> After encountering this problem, I failed over the node
> (restarted the SQL services, and writes became fast again,
> but this was only temporary (lasted a couple hours), which
> led me to believe that it could be a memory issue.
> Anyway, after that, I made the following changes: 1.
> Backed up the database, then the log, shrank the log
> (now .99MB) and temporarily put set the recovery mode to
> simple. 2. Reduced the max SQL server memory setting to 1
> G.
> Writes are again very fast. I have seen no problems for a
> day and a half under a normal load. It would really help
> to know if the large tlog could have actually been the
> problem? Or the if the memory settings may have done
> the trick and why? Then we can rest assured that the
> issue will not crop
> up again later!
> Thanks,
> Chris
>|||Thank you so much for your help.
Chris
>--Original Message--
>The log could be a problem. Obviously all the
uncommitted transactions are
>sitting in there, and writing in sequence could be tying
up things.
>Thinking along those lines, what you need to do is think
of a good recovery
>model or consider moving the transaction log to a
separate disk where there
>is nothing on it. I wish I could cite a source better
than Transcender
>study questions to say that the recommendation I've seen
is to put a
>transaction log on a mirrored set, but you can find some
discussions doing
>google searches.
>A good recovery model depends on whether you want up to
the minute restores
>or you can stand to lose everything from the last
full /differential backup.
>The frequent transaction log backups or truncations will
keep the size down
>to a manageable one.
>If you need up-to-the-minute restores, then consider
setting your database
>to the full recovery model, and schedule frequent
transaction log backups to
>disk. Then you can restore additional logs on top of the
last full database
>restore.
>If you can stand to lose everything from the last full
backup, then consider
>using the simple recovery model (select into/bulkcopy
true, trunc log on
>chkpt true), which will automatically empty your
transaction log at every
>checkpoint the database does. Then make sure your full
backups and
>differential backups are happening at appropriate
intervals. Good luck.
>--
>**********************************************************
*********
>Andy S.
>MCSE NT/2000, MCDBA SQL 7/2000
>andymcdba1@.NOMORESPAM.yahoo.com
>Please remove NOMORESPAM before replying.
>Always keep your antivirus and Microsoft software
>up to date with the latest definitions and product
updates.
>Be suspicious of every email attachment, I will never send
>or post anything other than the text of a http:// link nor
>post the link directly to a file for downloading.
>This posting is provided "as is" with no warranties
>and confers no rights.
>**********************************************************
*********
>"Christian Bouche" <cbouche@.gohealthcast.com> wrote in
message
>news:2a1801c3fc91$500f3fe0$a101280a@.phx.gbl...
>> Hi,
>> I really would appreciate any help on this. Can a very
>> large transaction log have a negative impact on write
>> performance' We were having a problem with VERY slow
>> writes (30 seconds each) to a database. Reads were very
>> fast (0 ms). The app is running on a Windows 2000/SQL
>> 2000 Active/Passive cluster. Each server has 2 G of ram
>> and disk space is absolutely not an issue. The only
other
>> applications that run on the server are Mcaffee (but
this
>> is disabled from the data/log directories).
>> We noticed that the log file for the affected database
was
>> EXTREMELY large (3G). TONS of leftover disk space for
the
>> log. There's not a whole lot of updates that occur in
>> this database. The database was in Full recovery mode.
>> We usually rely on nightly backups due to the sparse
>> update/inserts/deletes.
>> After encountering this problem, I failed over the node
>> (restarted the SQL services, and writes became fast
again,
>> but this was only temporary (lasted a couple hours),
which
>> led me to believe that it could be a memory issue.
>> Anyway, after that, I made the following changes: 1.
>> Backed up the database, then the log, shrank the log
>> (now .99MB) and temporarily put set the recovery mode to
>> simple. 2. Reduced the max SQL server memory setting
to 1
>> G.
>> Writes are again very fast. I have seen no problems
for a
>> day and a half under a normal load. It would really
help
>> to know if the large tlog could have actually been the
>> problem? Or the if the memory settings may have done
>> the trick and why? Then we can rest assured that the
>> issue will not crop
>> up again later!
>> Thanks,
>> Chris
>
>.
>

Wednesday, March 7, 2012

Large TempDB

Hi
I have many reports running from a SQL Server DB, from Crystal Enterprise
reporting system. However I have noticed some performance degradation, and
I noticed that my TempDB had ballooned in size. Is there a way to reset
this? I tried Shrink, but this did not reduce the size of the DB?
Kind Regards
Ricky
(SQL2K-SP3A/WIN2K-Client/WIN2K-Server/Crystal Enterprise 9.0)http://www.aspfaq.com/show.asp?id=2446
David Portas
SQL Server MVP
--
"rikesh patel" <trikky@.rukp28.freeserve.co.uk> wrote in message
news:ubgD8RnxFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have many reports running from a SQL Server DB, from Crystal Enterprise
> reporting system. However I have noticed some performance degradation,
> and
> I noticed that my TempDB had ballooned in size. Is there a way to reset
> this? I tried Shrink, but this did not reduce the size of the DB?
> Kind Regards
> Ricky
> (SQL2K-SP3A/WIN2K-Client/WIN2K-Server/Crystal Enterprise 9.0)
>

Large table/slow query/ can performance be improved?

I am having performance issues on a SQL query in Access. My query is
accessing and joining several tables (one very large one). The tables are
linked ODBC. The client submits the query to the server, separated by
several states. It appears the query is retrieving gigs of data from the
table and processing the joins on the client. Is there away to perform more
of the work on the server there by minimizing the amount of extraneous table
data moving across the network and improving performance (woefully slow
about 6 hours)?"Robert" <stop.spam@.whitehouse.gov> wrote in message
news:HLFKqK.MqD@.news.boeing.com...
> I am having performance issues on a SQL query in Access. My query is
> accessing and joining several tables (one very large one). The tables are
> linked ODBC. The client submits the query to the server, separated by
> several states. It appears the query is retrieving gigs of data from the
> table and processing the joins on the client. Is there away to perform
more
> of the work on the server there by minimizing the amount of extraneous
table
> data moving across the network and improving performance (woefully slow
> about 6 hours)?

Hmm, I haven't touched Access in years, but I recall it supporting what I
think it called Pass Thru Queries?

Or another option (perhaps ultimately your best one) is rewrite it as a
stored proc on the server.

If it's taking 6 hours, unless you're joining multigig table and doing
something like outer joins, I've got to imagine you're right, it's Access
trying to process things locally.
|||Hi,

These are some tips from my side.

1. Put indexes on column that is frequently used in query. This will
help database engine to search data quickly.
2. If the Recordset has less than 500 records, then the snapshot
Recordset will be faster than the dynaset Recordset.
3. Make query selection/criteria so as resultset will be small.

Finally, can you elaborate/post what exactly u r trying to do and
achive.

Amit

"Robert" <stop.spam@.whitehouse.gov> wrote in message news:<HLFKqK.MqD@.news.boeing.com>...
> I am having performance issues on a SQL query in Access. My query is
> accessing and joining several tables (one very large one). The tables are
> linked ODBC. The client submits the query to the server, separated by
> several states. It appears the query is retrieving gigs of data from the
> table and processing the joins on the client. Is there away to perform more
> of the work on the server there by minimizing the amount of extraneous table
> data moving across the network and improving performance (woefully slow
> about 6 hours)?|||As long as you're not using any client-side functions (Access or user-defined),
you should be able to make this into a Pass-Through query.

1) Copy the SQL Statement to the clipboard (or better yet, to Notepad)

2) In Access, make a new query, but don't choose any tables.

3) Choose Query/SQL Specific>Pass-Through

4) Paste the SQL Statement in there

5) Choose View/Properties

6) Set the Connect string (and ODBC Timeout!) appropriately

Run the query.

I have never seen an Access query take 6 hours to run...and I have an Access
database linked to over 68 MILLION records. Admittedly, I would never even WAIT
that long to find out if it could ever complete!

The slowest Access queries I've seen are the ones that I call "Query of a query
of a query...etc." with all records included the whole time, and a criteria at
the end; especially if Access or user-defined (VBA) function are invovled!

You really should learn the advantages of SQL Server's "SQL langauange" vs
Access. My favorite example is the FULL OUTER JOIN...with one statement in SQL
Server you get both sets of records from the eaither side of join whether or
not they match...can't do that in Access without making three queries! (OUTER,
Non-Match OUTER, then UNION)

Althogh the IIF in Access is convenient, it insists on evaluating both
outcomes...SQL Server's "CASE" construct makes much more sense.

If your query DOES use Access and/or user-defined VBA Functions, then you
should have the whole process converted to a stored procedure, and the use a
Pass-Through query to call the Stored Procedure. In all seriousness, EVERY
query you need should be converted to paramaterized Stored Procedures, and
called via Pass-Through...this is the best way to optimize performance.

CAVEAT: Pass-Through queries cannot be used as record sources for
linkchild/master situations (That's a JET-only feature), but you can always
program the same functionality yourself!

I'd be interested in seeing the SQL Statement of the 6-hr query.

Large table search performance

I have a large table with 300,000 rows and it will grow in the future
The table contains member profile details and it has many columns (about 70)
The stored procedure that do the search use many columns to search the results
That why the ‘Where’ is very long and the all operation take to many time, some time I get Time out exception form the SQL server.
I’ll be happy to get some ideas how to do this search.
I think maybe to divide the table to many tables by State.


Something interesting is that the operation takes long time for the first search and then the second and the third are much faster, and after a few min that I’m not do anything again the first time is very slow

70 columns in a table is probably too many. It is hard to say. But there are few options to consider:

1. You can partition the table vertically. For example, split the table vertically into two - one with the most frequently accessed columns and another with the infrequent ones. This will make each table smaller in terms of row size and queries will be faster than accessing a single wide table. This of course complicates the data modifications since you have to issue two DMLs as opposed to one but it depends on your application and nature of the table

2. You can use partitioning (horizontal). But it depends on the choice of your partitioning key whether it is part of every search criteria or at least one of the primary search conditions. You get some manageability benefit also.

Anyway, the approach depends on your needs. You can find whitepapers in MSDN that discuss partitioning for example. Please take a look at that also. You can leave the table as is also and try to optimize the queries by say adding more indexes or creating additional statistics or restricting the search columns. Each has it's pros and cons. For example, adding more indexes will slow down data modification operations.

The reason why the queries are faster after the first time is due to plan caching. Looks like you are generating lot of ad-hoc queries due to variable number of search conditions and this is expected. Of course, the plan might get removed from cache later due to memory pressure or lack of reusability. It is hard to tell without knowing the type of queries and the nature of the workload.

|||

What type of partition will give me the best performance vertical or horizontal?

In horizontal partition what is the best way to do it:

How mane tables?

Witch column on each table?

The query that do all the problems is the search query and it use 14 columns in the ‘WHERE’ statement, maybe it best to divide the table into 2 table that one will hold the 14 columns for the search query and the second table will hold the rest of the columns is this will give best performance?

Large table search performance

I have a large table with 300,000 rows and it will grow in the future
The table contains member profile details and it has many columns (about 70)
The stored procedure that do the search use many columns to search the results
That why the ‘Where’ is very long and the all operation take to many time, some time I get Time out exception form the SQL server.
I’ll be happy to get some ideas how to do this search.
I think maybe to divide the table to many tables by State.


Something interesting is that the operation takes long time for the first search and then the second and the third are much faster, and after a few min that I’m not do anything again the first time is very slow

70 columns in a table is probably too many. It is hard to say. But there are few options to consider:

1. You can partition the table vertically. For example, split the table vertically into two - one with the most frequently accessed columns and another with the infrequent ones. This will make each table smaller in terms of row size and queries will be faster than accessing a single wide table. This of course complicates the data modifications since you have to issue two DMLs as opposed to one but it depends on your application and nature of the table

2. You can use partitioning (horizontal). But it depends on the choice of your partitioning key whether it is part of every search criteria or at least one of the primary search conditions. You get some manageability benefit also.

Anyway, the approach depends on your needs. You can find whitepapers in MSDN that discuss partitioning for example. Please take a look at that also. You can leave the table as is also and try to optimize the queries by say adding more indexes or creating additional statistics or restricting the search columns. Each has it's pros and cons. For example, adding more indexes will slow down data modification operations.

The reason why the queries are faster after the first time is due to plan caching. Looks like you are generating lot of ad-hoc queries due to variable number of search conditions and this is expected. Of course, the plan might get removed from cache later due to memory pressure or lack of reusability. It is hard to tell without knowing the type of queries and the nature of the workload.

|||

What type of partition will give me the best performance vertical or horizontal?

In horizontal partition what is the best way to do it:

How mane tables?

Witch column on each table?

The query that do all the problems is the search query and it use 14 columns in the ‘WHERE’ statement, maybe it best to divide the table into 2 table that one will hold the 14 columns for the search query and the second table will hold the rest of the columns is this will give best performance?

Large table performance

I have a table that will be the most used table in a system that I am
developing. The table will be used by around 10 users for both read
and write operations and is likely that it might grow to have millions
of records and it will have around 6 columns, most of them numeric.
I was considering partitioning this table horizontally but that might
make the front end application a lot more difficult to develop.
So I wonder if proper indexing the table or any other database trick
can allow me to keep a good performance on this table or if I
necessarily should partition. Ideas anybody?On Fri, 15 Jun 2007 17:31:19 -0700, Artificer
<eliezerfigueroa@.gmail.com> wrote:

>I have a table that will be the most used table in a system that I am
>developing. The table will be used by around 10 users for both read
>and write operations and is likely that it might grow to have millions
>of records and it will have around 6 columns, most of them numeric.
>I was considering partitioning this table horizontally but that might
>make the front end application a lot more difficult to develop.
>So I wonder if proper indexing the table or any other database trick
>can allow me to keep a good performance on this table or if I
>necessarily should partition. Ideas anybody?
What you describe does not sound particularly demanding. Ten users is
not many. Six columns is narrow. Millions of narrow rows simply
means it needs the proper indexing. You are a long way from having to
consider partitioning, though it is is always something to keep in
mind if performance problems occur that are not fixed with simpler
measures.
If you post the table definition, describe the data, and outline the
usage you will probably get some good advice on indexing the table.
Roy Harvey
Beacon Falls, CT|||An index is actually an ordered set of paritions. Think about it - the first
column in the index groups all rows with the same values. The second column
groups rows of equal values as sub-ranges of the prior column and so on.
Assuming you design the right index/s for your query/s you should never have
a need to partition, unless you need to load & unload substantial numbers of
rows en-mass.
Regards,
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
http://www.SQLBenchmarkPro.com
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1181953879.072353.197600@.m36g2000hse.googlegroups.com...
>I have a table that will be the most used table in a system that I am
> developing. The table will be used by around 10 users for both read
> and write operations and is likely that it might grow to have millions
> of records and it will have around 6 columns, most of them numeric.
> I was considering partitioning this table horizontally but that might
> make the front end application a lot more difficult to develop.
> So I wonder if proper indexing the table or any other database trick
> can allow me to keep a good performance on this table or if I
> necessarily should partition. Ideas anybody?
>

Large table performance

I have a table that will be the most used table in a system that I am
developing. The table will be used by around 10 users for both read
and write operations and is likely that it might grow to have millions
of records and it will have around 6 columns, most of them numeric.
I was considering partitioning this table horizontally but that might
make the front end application a lot more difficult to develop.
So I wonder if proper indexing the table or any other database trick
can allow me to keep a good performance on this table or if I
necessarily should partition. Ideas anybody?
On Fri, 15 Jun 2007 17:31:19 -0700, Artificer
<eliezerfigueroa@.gmail.com> wrote:

>I have a table that will be the most used table in a system that I am
>developing. The table will be used by around 10 users for both read
>and write operations and is likely that it might grow to have millions
>of records and it will have around 6 columns, most of them numeric.
>I was considering partitioning this table horizontally but that might
>make the front end application a lot more difficult to develop.
>So I wonder if proper indexing the table or any other database trick
>can allow me to keep a good performance on this table or if I
>necessarily should partition. Ideas anybody?
What you describe does not sound particularly demanding. Ten users is
not many. Six columns is narrow. Millions of narrow rows simply
means it needs the proper indexing. You are a long way from having to
consider partitioning, though it is is always something to keep in
mind if performance problems occur that are not fixed with simpler
measures.
If you post the table definition, describe the data, and outline the
usage you will probably get some good advice on indexing the table.
Roy Harvey
Beacon Falls, CT

Large table performance

I have a table that will be the most used table in a system that I am
developing. The table will be used by around 10 users for both read
and write operations and is likely that it might grow to have millions
of records and it will have around 6 columns, most of them numeric.
I was considering partitioning this table horizontally but that might
make the front end application a lot more difficult to develop.
So I wonder if proper indexing the table or any other database trick
can allow me to keep a good performance on this table or if I
necessarily should partition. Ideas anybody?On Fri, 15 Jun 2007 17:31:19 -0700, Artificer
<eliezerfigueroa@.gmail.com> wrote:
>I have a table that will be the most used table in a system that I am
>developing. The table will be used by around 10 users for both read
>and write operations and is likely that it might grow to have millions
>of records and it will have around 6 columns, most of them numeric.
>I was considering partitioning this table horizontally but that might
>make the front end application a lot more difficult to develop.
>So I wonder if proper indexing the table or any other database trick
>can allow me to keep a good performance on this table or if I
>necessarily should partition. Ideas anybody?
What you describe does not sound particularly demanding. Ten users is
not many. Six columns is narrow. Millions of narrow rows simply
means it needs the proper indexing. You are a long way from having to
consider partitioning, though it is is always something to keep in
mind if performance problems occur that are not fixed with simpler
measures.
If you post the table definition, describe the data, and outline the
usage you will probably get some good advice on indexing the table.
Roy Harvey
Beacon Falls, CT|||An index is actually an ordered set of paritions. Think about it - the first
column in the index groups all rows with the same values. The second column
groups rows of equal values as sub-ranges of the prior column and so on.
Assuming you design the right index/s for your query/s you should never have
a need to partition, unless you need to load & unload substantial numbers of
rows en-mass.
Regards,
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
http://www.SQLBenchmarkPro.com
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1181953879.072353.197600@.m36g2000hse.googlegroups.com...
>I have a table that will be the most used table in a system that I am
> developing. The table will be used by around 10 users for both read
> and write operations and is likely that it might grow to have millions
> of records and it will have around 6 columns, most of them numeric.
> I was considering partitioning this table horizontally but that might
> make the front end application a lot more difficult to develop.
> So I wonder if proper indexing the table or any other database trick
> can allow me to keep a good performance on this table or if I
> necessarily should partition. Ideas anybody?
>

Friday, February 24, 2012

Large String Parameters Causing Performance Issues

Hi All,
Bit of strange one this. I have a key field in one of my databases that is a
Char(18). I use this as a parameter field in one of my reports.
When I run the report and specify the key field parameter manually the
report runs instantly. If I attempt to set this as a parameter variable and
enter the criteria in the parameter box, the report takes over a minute to
run. The 2 statements are as follows.
1) select sum(sales_value) as sales from production.dbo.epos_transactions
where transaction_ref = '085505010500074995'
When run as above, the report runs instantly.
2) select sum(sales_value) as sales from production.dbo.epos_transactions
where transaction_ref = @.TREF
When I enter the parameter and run the report it takes over a minute to run.
Running the query through query analyser causes no issues on any of the
servers involved and the response time on all servers is instant.
I'd be grateful if anyone can shed any light on this.
Many thanks for any help.
--
Nick Colebourn
DBA
United Coop LtdHi Nick,
Could you supply a generic repro for this issue. An RDL for Northwind or a
simple DB. I would love to take a look at it.
| Thread-Topic: Large String Parameters Causing Performance Issues
| thread-index: AcUC1GzdZAw/tJ+RRciPnzP3TypLNA==| X-WBNR-Posting-Host: 213.146.135.115
| From: "=?Utf-8?B?TmljayBDb2xlYm91cm4=?="
<NickColebourn@.discussions.microsoft.com>
| Subject: Large String Parameters Causing Performance Issues
| Date: Tue, 25 Jan 2005 03:53:03 -0800
| Lines: 29
| Message-ID: <EFE8F146-E9CC-49C8-A5B8-4E29E3F57EC2@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:40737
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi All,
| Bit of strange one this. I have a key field in one of my databases that
is a
| Char(18). I use this as a parameter field in one of my reports.
| When I run the report and specify the key field parameter manually the
| report runs instantly. If I attempt to set this as a parameter variable
and
| enter the criteria in the parameter box, the report takes over a minute
to
| run. The 2 statements are as follows.
|
| 1) select sum(sales_value) as sales from production.dbo.epos_transactions
| where transaction_ref = '085505010500074995'
|
| When run as above, the report runs instantly.
|
| 2) select sum(sales_value) as sales from production.dbo.epos_transactions
| where transaction_ref = @.TREF
|
| When I enter the parameter and run the report it takes over a minute to
run.
|
| Running the query through query analyser causes no issues on any of the
| servers involved and the response time on all servers is instant.
|
| I'd be grateful if anyone can shed any light on this.
|
| Many thanks for any help.
|
| --
| Nick Colebourn
| DBA
| United Coop Ltd
||||Hi Brad,
Many thanks for your reply. I'll see if I can get it to do the same
with a northwind query and post back asap. For your info, I've created an
in-line table valued function containing the sql with the parameter and
called this as the dataset instead of the sql text itself. This works ok
either with static criteria or using a parameter passed across to the
function. The performance issue only seems to happen when you pass the
parameter value directly across to the sql text.
Many thanks
Nick
""Brad Syputa - MS"" wrote:
> Hi Nick,
> Could you supply a generic repro for this issue. An RDL for Northwind or a
> simple DB. I would love to take a look at it.
>
> --
> | Thread-Topic: Large String Parameters Causing Performance Issues
> | thread-index: AcUC1GzdZAw/tJ+RRciPnzP3TypLNA==> | X-WBNR-Posting-Host: 213.146.135.115
> | From: "=?Utf-8?B?TmljayBDb2xlYm91cm4=?="
> <NickColebourn@.discussions.microsoft.com>
> | Subject: Large String Parameters Causing Performance Issues
> | Date: Tue, 25 Jan 2005 03:53:03 -0800
> | Lines: 29
> | Message-ID: <EFE8F146-E9CC-49C8-A5B8-4E29E3F57EC2@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:40737
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi All,
> | Bit of strange one this. I have a key field in one of my databases that
> is a
> | Char(18). I use this as a parameter field in one of my reports.
> | When I run the report and specify the key field parameter manually the
> | report runs instantly. If I attempt to set this as a parameter variable
> and
> | enter the criteria in the parameter box, the report takes over a minute
> to
> | run. The 2 statements are as follows.
> |
> | 1) select sum(sales_value) as sales from production.dbo.epos_transactions
> | where transaction_ref = '085505010500074995'
> |
> | When run as above, the report runs instantly.
> |
> | 2) select sum(sales_value) as sales from production.dbo.epos_transactions
> | where transaction_ref = @.TREF
> |
> | When I enter the parameter and run the report it takes over a minute to
> run.
> |
> | Running the query through query analyser causes no issues on any of the
> | servers involved and the response time on all servers is instant.
> |
> | I'd be grateful if anyone can shed any light on this.
> |
> | Many thanks for any help.
> |
> | --
> | Nick Colebourn
> | DBA
> | United Coop Ltd
> |
>