Performance and Large Data

Hi *.*
I've a table with 14 columns and 640.000 rows, a simple select (* from
table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
Is it normal? How can I reduce this time? With some reports that use
this table, I've use Index Tuning Wizard and performance is now a
little better but I don't know if this is a limitation of SQL Server or
limitation of my knowledge? (SQL Server 2000-SP4 on Windows XP Pro)
Would you plz kindly help me with your tips and advices?
Thanks in Advance,
HamedHamed,
pls can you post up your query, the table schema, tell us what indexes you
have at present and the showplan output from running the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Are you saying that you return 640,000 rows? If so:
Why? Who can make heads or tails out of 640,000 rows? Without doing somethin
g reasonable, like
filtering, aggregating to cut down number of rows to be returned, there's no
t much to be done. One
thing that can be interesting is the Query Analyzer is much quicker to print
the result in grid
compared to text mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hamed" <hoveisi@.gmail.com> wrote in message
news:1155289810.440647.110830@.i3g2000cwc.googlegroups.com...
> Hi *.*
> I've a table with 14 columns and 640.000 rows, a simple select (* from
> table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
> Is it normal? How can I reduce this time? With some reports that use
> this table, I've use Index Tuning Wizard and performance is now a
> little better but I don't know if this is a limitation of SQL Server or
> limitation of my knowledge? (SQL Server 2000-SP4 on Windows XP Pro)
> Would you plz kindly help me with your tips and advices?
> Thanks in Advance,
> Hamed
>|||On 11 Aug 2006 02:50:10 -0700, "Hamed" <hoveisi@.gmail.com> wrote:
> I've a table with 14 columns and 640.000 rows, a simple select (* from
>table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
>Is it normal? How can I reduce this time? With some reports that use
>this table, I've use Index Tuning Wizard and performance is now a
>little better but I don't know if this is a limitation of SQL Server or
>limitation of my knowledge? (SQL Server 2000-SP4 on Windows XP Pro)
If it has to read and compare the rows to some kind of where clause,
or if you have no where clause and it has to return all 640k rows,
which is probably at least 60mb, it just takes that long to move the
data around.
If your logic is such that the SQLServer optimizer thinks it needs to
scan the table and not use any available indexes, then it is simply
going to take a while. OTOH, if you have 750mb of RAM, it may be that
the first time you run the select, it runs slowly because it is doing
physical disk I/O, but if you rerun the same query immediately it will
run much, much faster because ALL of the data is cached in RAM.
Hope that helps.
J.

Performance and Large Data

Hi *.*
I've a table with 14 columns and 640.000 rows, a simple select (* from
table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
Is it normal? How can I reduce this time? With some reports that use
this table, I've use Index Tuning Wizard and performance is now a
little better but I don't know if this is a limitation of SQL Server or
limitation of my knowledge? (SQL Server 2000-SP4 on WIndows XP Pro)
Would you plz kindly help me with your tips and advices?
Thanks in Advance,
HamedHamed,
pls can you post up your query, the table schema, tell us what indexes you
have at present and the showplan output from running the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Are you saying that you return 640,000 rows? If so:
Why? Who can make heads or tails out of 640,000 rows? Without doing something reasonable, like
filtering, aggregating to cut down number of rows to be returned, there's not much to be done. One
thing that can be interesting is the Query Analyzer is much quicker to print the result in grid
compared to text mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hamed" <hoveisi@.gmail.com> wrote in message
news:1155289810.440647.110830@.i3g2000cwc.googlegroups.com...
> Hi *.*
> I've a table with 14 columns and 640.000 rows, a simple select (* from
> table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
> Is it normal? How can I reduce this time? With some reports that use
> this table, I've use Index Tuning Wizard and performance is now a
> little better but I don't know if this is a limitation of SQL Server or
> limitation of my knowledge? (SQL Server 2000-SP4 on WIndows XP Pro)
> Would you plz kindly help me with your tips and advices?
> Thanks in Advance,
> Hamed
>|||On 11 Aug 2006 02:50:10 -0700, "Hamed" <hoveisi@.gmail.com> wrote:
> I've a table with 14 columns and 640.000 rows, a simple select (* from
>table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
>Is it normal? How can I reduce this time? With some reports that use
>this table, I've use Index Tuning Wizard and performance is now a
>little better but I don't know if this is a limitation of SQL Server or
>limitation of my knowledge? (SQL Server 2000-SP4 on WIndows XP Pro)
If it has to read and compare the rows to some kind of where clause,
or if you have no where clause and it has to return all 640k rows,
which is probably at least 60mb, it just takes that long to move the
data around.
If your logic is such that the SQLServer optimizer thinks it needs to
scan the table and not use any available indexes, then it is simply
going to take a while. OTOH, if you have 750mb of RAM, it may be that
the first time you run the select, it runs slowly because it is doing
physical disk I/O, but if you rerun the same query immediately it will
run much, much faster because ALL of the data is cached in RAM.
Hope that helps.
J.

Performance and Index Size

Hello:
We are testing SQL Server performance on XML columns and we have poor
perfomance, compared with another database (DB2). My questions are: a) is
there something we can do to improve performance; b) is there anything we ca
n
do to decrease index size.
We used big XML documents (40 Kb), small - medium records (5000 records) and
JDBC.
Results are the following:
Insert 5000 records: 1,162,859 sec. (Oracle) vs. 383,953 sec. (DB2)
Counting 5000 records: 21,188 sec. (Oracle) vs. 1,265 sec. (DB2)
Index size: 1.66 GB (Oracle) vs. 700 Mb (DB2)
It's worth noting that given the size of XML documents, the full indexed
scenario (primary index and all secondary indexes defined) could not be
tested, because it took more than 45 min. to insert 5000 records and ended
with a disk full error, consuming more than 2.5 GB.
We tried two queries: insert and counting. To insert we used the following
instruction:
INSERT INTO TEST VALUES (?, ?) - first parameter is int, second a string
with XML content.
To count we used the following query:
SELECT COUNT(*) FROM (
SELECT info.value('
declare default element namespace "urn:hl7-org:v3";
(/ClinicalDocument/component/structuredBody/component[7]/section/entry/obser
vation[code/@.code="550.90"]/effectiveTime/@.value)[1]
', 'nvarchar(1000)') YR FROM test) T
GROUP BY YR
ORDER BY YR DESC;
Index were created with the following instructions:
CREATE PRIMARY XML INDEX PIdx_TEST_info ON test(info)
CREATE XML INDEX PIdx_TEST_info_PATH ON test(info)
USING XML INDEX PIdx_TEST_info
FOR PATH
Thanks in advance for your help,
JavierHello Javier,
a.) Yes, defer creating the index until after all of your data has been inse
rted.
b.) Yes, only define the indexes that you know you are going to need.
Okay, so I know that A isn't a great answer, but honestly, it might be the
best choice you have here. Remember that the primary XML index persists the
node table to index space so every insert has to do the expensive parsing
and then maintain the index. You might be better of dropping and recreating
it after each bulk insert.
Schema binding make help the index be more space efficient due to typing,
but it looks like you're storing HL7 data. I can't imagine that SQL Server
is going to enjoy that schema very much.
Once you have the primary XML index beaten into submission, then you really
should decide and test if you need all of the other XML indexes or not. Its
very wasteful to define an index you don't need or use.
Its really not the size that's the issue here so much as it is the number
of elements and attributes within each instance and the relative amount of
useful data per node. Again if I'm guessing right in your 40k of data, you
probably have hundreds of elements and attributes. Since each of these becom
es
a row in the node table, you're probably generating lots of rows with little
data in them.
I'd also have to wonder how much or little your JDBC driver is helping you
here, but that's topic for another NG.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Javier
I would be interested in your test setup. Could you please contact me at my
email (remove the online from the address)?
Thanks
Michael
"Javier Diaz" <JavierDiaz@.discussions.microsoft.com> wrote in message
news:DF70B1FD-B8A5-4F6F-8CAA-865104B6572E@.microsoft.com...
> Hello:
> We are testing SQL Server performance on XML columns and we have poor
> perfomance, compared with another database (DB2). My questions are: a) is
> there something we can do to improve performance; b) is there anything we
> can
> do to decrease index size.
> We used big XML documents (40 Kb), small - medium records (5000 records)
> and
> JDBC.
> Results are the following:
> Insert 5000 records: 1,162,859 sec. (Oracle) vs. 383,953 sec. (DB2)
> Counting 5000 records: 21,188 sec. (Oracle) vs. 1,265 sec. (DB2)
> Index size: 1.66 GB (Oracle) vs. 700 Mb (DB2)
> It's worth noting that given the size of XML documents, the full indexed
> scenario (primary index and all secondary indexes defined) could not be
> tested, because it took more than 45 min. to insert 5000 records and ended
> with a disk full error, consuming more than 2.5 GB.
> We tried two queries: insert and counting. To insert we used the following
> instruction:
> INSERT INTO TEST VALUES (?, ?) - first parameter is int, second a string
> with XML content.
> To count we used the following query:
> SELECT COUNT(*) FROM (
> SELECT info.value('
> declare default element namespace "urn:hl7-org:v3";
> (/ClinicalDocument/component/structuredBody/component[7]/section/entry/obs
ervation[code/@.code="550.90"]/effectiveTime/@.value)[1]
> ', 'nvarchar(1000)') YR FROM test) T
> GROUP BY YR
> ORDER BY YR DESC;
> Index were created with the following instructions:
> CREATE PRIMARY XML INDEX PIdx_TEST_info ON test(info)
> CREATE XML INDEX PIdx_TEST_info_PATH ON test(info)
> USING XML INDEX PIdx_TEST_info
> FOR PATH
> Thanks in advance for your help,
> Javier
>

Performance and Index Size

Hello:
We are testing SQL Server performance on XML columns and we have poor
perfomance, compared with another database (DB2). My questions are: a) is
there something we can do to improve performance; b) is there anything we can
do to decrease index size.
We used big XML documents (40 Kb), small - medium records (5000 records) and
JDBC.
Results are the following:
Insert 5000 records: 1,162,859 sec. (Oracle) vs. 383,953 sec. (DB2)
Counting 5000 records: 21,188 sec. (Oracle) vs. 1,265 sec. (DB2)
Index size: 1.66 GB (Oracle) vs. 700 Mb (DB2)
It's worth noting that given the size of XML documents, the full indexed
scenario (primary index and all secondary indexes defined) could not be
tested, because it took more than 45 min. to insert 5000 records and ended
with a disk full error, consuming more than 2.5 GB.
We tried two queries: insert and counting. To insert we used the following
instruction:
INSERT INTO TEST VALUES (?, ?) - first parameter is int, second a string
with XML content.
To count we used the following query:
SELECT COUNT(*) FROM (
SELECT info.value('
declare default element namespace "urn:hl7-org:v3";
(/ClinicalDocument/component/structuredBody/component[7]/section/entry/observation[code/@.code="550.90"]/effectiveTime/@.value)[1]
', 'nvarchar(1000)') YR FROM test) T
GROUP BY YR
ORDER BY YR DESC;
Index were created with the following instructions:
CREATE PRIMARY XML INDEX PIdx_TEST_info ON test(info)
CREATE XML INDEX PIdx_TEST_info_PATH ON test(info)
USING XML INDEX PIdx_TEST_info
FOR PATH
Thanks in advance for your help,
Javier
Hello Javier,
a.) Yes, defer creating the index until after all of your data has been inserted.
b.) Yes, only define the indexes that you know you are going to need.
Okay, so I know that A isn't a great answer, but honestly, it might be the
best choice you have here. Remember that the primary XML index persists the
node table to index space so every insert has to do the expensive parsing
and then maintain the index. You might be better of dropping and recreating
it after each bulk insert.
Schema binding make help the index be more space efficient due to typing,
but it looks like you're storing HL7 data. I can't imagine that SQL Server
is going to enjoy that schema very much.
Once you have the primary XML index beaten into submission, then you really
should decide and test if you need all of the other XML indexes or not. Its
very wasteful to define an index you don't need or use.
Its really not the size that's the issue here so much as it is the number
of elements and attributes within each instance and the relative amount of
useful data per node. Again if I'm guessing right in your 40k of data, you
probably have hundreds of elements and attributes. Since each of these becomes
a row in the node table, you're probably generating lots of rows with little
data in them.
I'd also have to wonder how much or little your JDBC driver is helping you
here, but that's topic for another NG.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Hi Javier
I would be interested in your test setup. Could you please contact me at my
email (remove the online from the address)?
Thanks
Michael
"Javier Diaz" <JavierDiaz@.discussions.microsoft.com> wrote in message
news:DF70B1FD-B8A5-4F6F-8CAA-865104B6572E@.microsoft.com...
> Hello:
> We are testing SQL Server performance on XML columns and we have poor
> perfomance, compared with another database (DB2). My questions are: a) is
> there something we can do to improve performance; b) is there anything we
> can
> do to decrease index size.
> We used big XML documents (40 Kb), small - medium records (5000 records)
> and
> JDBC.
> Results are the following:
> Insert 5000 records: 1,162,859 sec. (Oracle) vs. 383,953 sec. (DB2)
> Counting 5000 records: 21,188 sec. (Oracle) vs. 1,265 sec. (DB2)
> Index size: 1.66 GB (Oracle) vs. 700 Mb (DB2)
> It's worth noting that given the size of XML documents, the full indexed
> scenario (primary index and all secondary indexes defined) could not be
> tested, because it took more than 45 min. to insert 5000 records and ended
> with a disk full error, consuming more than 2.5 GB.
> We tried two queries: insert and counting. To insert we used the following
> instruction:
> INSERT INTO TEST VALUES (?, ?) - first parameter is int, second a string
> with XML content.
> To count we used the following query:
> SELECT COUNT(*) FROM (
> SELECT info.value('
> declare default element namespace "urn:hl7-org:v3";
> (/ClinicalDocument/component/structuredBody/component[7]/section/entry/observation[code/@.code="550.90"]/effectiveTime/@.value)[1]
> ', 'nvarchar(1000)') YR FROM test) T
> GROUP BY YR
> ORDER BY YR DESC;
> Index were created with the following instructions:
> CREATE PRIMARY XML INDEX PIdx_TEST_info ON test(info)
> CREATE XML INDEX PIdx_TEST_info_PATH ON test(info)
> USING XML INDEX PIdx_TEST_info
> FOR PATH
> Thanks in advance for your help,
> Javier
>

Performance -- count(*)

I need to update the columns to blanks if it has Nulls; Most of the
time we do not have Nulls and to check that in a 3.2 million table it
takes 2 minutes and 45 seconds. (To do select count(*) from <table>
where <columnname> is Null) .

I just cut and pasted a part from my stored procedure:

EXEC ('IF (SELECT COUNT (*) FROM ' + @.tblname + ' WHERE '
+@.columnname + ' is Null ) >0
BEGIN
PRINT ''' + @.columnName + ' has Null ''' +
' UPDATE ' + @.tblname +
' SET ' + @.columnName + ' = ''''
WHERE ' + @.columnName + ' is Null
PRINT ''Updated ''
END'

Please let me know if there is a better way to do instead of doing
count (*).

Thanks in advance!<gelangov@.hotmail.com> wrote in message
news:1110828271.415880.244350@.l41g2000cwc.googlegr oups.com...
>I need to update the columns to blanks if it has Nulls; Most of the
> time we do not have Nulls and to check that in a 3.2 million table it
> takes 2 minutes and 45 seconds. (To do select count(*) from <table>
> where <columnname> is Null) .
> I just cut and pasted a part from my stored procedure:
> EXEC ('IF (SELECT COUNT (*) FROM ' + @.tblname + ' WHERE '
> +@.columnname + ' is Null ) >0
> BEGIN
> PRINT ''' + @.columnName + ' has Null ''' +
> ' UPDATE ' + @.tblname +
> ' SET ' + @.columnName + ' = ''''
> WHERE ' + @.columnName + ' is Null
> PRINT ''Updated ''
> END'
> Please let me know if there is a better way to do instead of doing
> count (*).
> Thanks in advance!

If you only want to know that at least one row has NULL, then EXISTS should
be much faster:

if exists (
select *
from dbo.MyTable
where MyColumn is null
)
begin
-- do UPDATE here
end

But if you're updating all your NULLs to empty strings, you might want to
consider a default on the column:

create table dbo.MyTable (
...
MyColumn varchar(100) not null default '',
...
)

Or if NULL is correct in your data model, and you only want empty strings
for output purposes, then you could use COALESCE() to provide the empty
string when you query the data.

Simon|||> Please let me know if there is a better way to do instead of doing
> count (*).

can you not use defaults or an insert/update trigger? This is doing an awful
lot of work on the server.

Even with exists, if it fails you will have scanned the entire table. and if
it suceeds quickly youll scan it anyway with the update gaining no more than
a 0-50% reduction in runtime only if there are nulls found. You may be able
to speed up a specific example with an index but there is probably an
'elegant' solution rather than relying on brute force or dedicated indexes.

Mr Tea

<gelangov@.hotmail.com> wrote in message
news:1110828271.415880.244350@.l41g2000cwc.googlegr oups.com...
>I need to update the columns to blanks if it has Nulls; Most of the
> time we do not have Nulls and to check that in a 3.2 million table it
> takes 2 minutes and 45 seconds. (To do select count(*) from <table>
> where <columnname> is Null) .
> I just cut and pasted a part from my stored procedure:
> EXEC ('IF (SELECT COUNT (*) FROM ' + @.tblname + ' WHERE '
> +@.columnname + ' is Null ) >0
> BEGIN
> PRINT ''' + @.columnName + ' has Null ''' +
> ' UPDATE ' + @.tblname +
> ' SET ' + @.columnName + ' = ''''
> WHERE ' + @.columnName + ' is Null
> PRINT ''Updated ''
> END'
> Please let me know if there is a better way to do instead of doing
> count (*).
> Thanks in advance!|||(gelangov@.hotmail.com) writes:
> I need to update the columns to blanks if it has Nulls; Most of the
> time we do not have Nulls and to check that in a 3.2 million table it
> takes 2 minutes and 45 seconds. (To do select count(*) from <table>
> where <columnname> is Null) .
> I just cut and pasted a part from my stored procedure:
> EXEC ('IF (SELECT COUNT (*) FROM ' + @.tblname + ' WHERE '
> +@.columnname + ' is Null ) >0
> BEGIN
> PRINT ''' + @.columnName + ' has Null ''' +
> ' UPDATE ' + @.tblname +
> ' SET ' + @.columnName + ' = ''''
> WHERE ' + @.columnName + ' is Null
> PRINT ''Updated ''
> END'
> Please let me know if there is a better way to do instead of doing
> count (*).

Since this is likely to cause a table scan, the best is just to submit
the UPDATE statement. If you want to know whether the table was updated
or not, you can catch @.@.rowcount when you are done. (And this can be
done outside the dynamic SQL.)

Then again, why not just say:

ALTER TABLE tbl ALTER COLUMN col <datatype> NOT NULL DEFAULT ''

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you everyone for all your replies.
Is there a performance difference for inserts when we make it Not Null
default ''VS when it is Null? We will be inserting a lot of data

Thanks
Erland Sommarskog wrote:
> (gelangov@.hotmail.com) writes:
> > I need to update the columns to blanks if it has Nulls; Most of the
> > time we do not have Nulls and to check that in a 3.2 million table
it
> > takes 2 minutes and 45 seconds. (To do select count(*) from <table>
> > where <columnname> is Null) .
> > I just cut and pasted a part from my stored procedure:
> > EXEC ('IF (SELECT COUNT (*) FROM ' + @.tblname + ' WHERE '
> > +@.columnname + ' is Null ) >0
> > BEGIN
> > PRINT ''' + @.columnName + ' has Null ''' +
> > ' UPDATE ' + @.tblname +
> > ' SET ' + @.columnName + ' = ''''
> > WHERE ' + @.columnName + ' is Null
> > PRINT ''Updated ''
> > END'
> > Please let me know if there is a better way to do instead of doing
> > count (*).
> Since this is likely to cause a table scan, the best is just to
submit
> the UPDATE statement. If you want to know whether the table was
updated
> or not, you can catch @.@.rowcount when you are done. (And this can be
> done outside the dynamic SQL.)
> Then again, why not just say:
> ALTER TABLE tbl ALTER COLUMN col <datatype> NOT NULL DEFAULT ''
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||(gelangov@.hotmail.com) writes:
> Thank you everyone for all your replies.
> Is there a performance difference for inserts when we make it Not Null
> default ''VS when it is Null? We will be inserting a lot of data

I don't remember exactly how NULL varchar values are stored. Possibly the ''
take up two bytes more than the NULL.

In any case, my opinion for all considerations like these is that you
should foremost look at this from a functional and logical point of view.
If all your values are known, but can be known to be empty use ''. If you
can have unknown values, permit NULL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Performance

Hello Everybody,
We have arround 10 reports, i have written store procedures and i have
created proper indexes on all the required columns. When i run specific
report, it takes around 40 secondes but when around 20 users log in to the
system (we have microsoft reporting server as a reporting tool and sql serve
r
2000 as a DB)
and start executing reports in that case the report which is taking around
40 seconds(when around couple of users log in to the system) takes around 10
minutes to run.
Here in all the reports we do only reads, no write operation and i do not
have any transactions in any of report store procedure.
I know when more than one person try to run same store procedure
at the same time in that case all the underlying table get shared lock.
I have checked DB Server CPU utilization, it is quiet normal.
So clearly it looks like when many users log in to the system, report
execution becomes very slow.
so what are the possible causes ?
Pls let me know.mvp wrote:
> Hello Everybody,
> We have arround 10 reports, i have written store procedures and i have
> created proper indexes on all the required columns. When i run
> specific report, it takes around 40 secondes but when around 20 users
> log in to the system (we have microsoft reporting server as a
> reporting tool and sql server 2000 as a DB)
> and start executing reports in that case the report which is taking
> around 40 seconds(when around couple of users log in to the system)
> takes around 10 minutes to run.
> Here in all the reports we do only reads, no write operation and i do
> not have any transactions in any of report store procedure.
>
> I know when more than one person try to run same store procedure
> at the same time in that case all the underlying table get shared
> lock.
> I have checked DB Server CPU utilization, it is quiet normal.
>
> So clearly it looks like when many users log in to the system, report
> execution becomes very slow.
> so what are the possible causes ?
Tons. Just to name a few
- sub optimal indexes (check execution plans)
- too few mem for SQL Server
- too much mem for SQL Server (-> paging)
- slow disks
- users select different data sets which reduce cache efficiency
- not enough CPU power
- locking
...
I'd start by using Profiler to see what's slow, and then dig further
probably using perfmon.
Good luck
robert|||Low CPU utilization in a case like this usually suggests another bottleneck.
Most likely disks or memory. If the reports are strictly read only and the
data is not being changed you might want to consider using the Read
Uncommitted transaction Isolation Level for the reports. It's not to reduce
blocking since you shouldn't have any but more to reduce the number of locks
and free up resources associated with them. Here are some links that should
help to find the bottlenecks:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:6688A51E-0414-4D52-B1C0-339F153ED13C@.microsoft.com...
> Hello Everybody,
> We have arround 10 reports, i have written store procedures and i have
> created proper indexes on all the required columns. When i run specific
> report, it takes around 40 secondes but when around 20 users log in to the
> system (we have microsoft reporting server as a reporting tool and sql
> server
> 2000 as a DB)
> and start executing reports in that case the report which is taking around
> 40 seconds(when around couple of users log in to the system) takes around
> 10
> minutes to run.
> Here in all the reports we do only reads, no write operation and i do not
> have any transactions in any of report store procedure.
>
> I know when more than one person try to run same store procedure
> at the same time in that case all the underlying table get shared lock.
> I have checked DB Server CPU utilization, it is quiet normal.
>
> So clearly it looks like when many users log in to the system, report
> execution becomes very slow.
> so what are the possible causes ?
> Pls let me know.|||The most likely reason the reports run slower while other users are accessin
g
the db is locking. The default transaction isolation for SQL is read
committed. The report (reader) is being blocked by the other users (writers)
who have uncommitted transactions. Using sp_who2 while the report is running
will show if the problem is due to locking.
If you are willing to accept the possibility of transactionally inconsistent
data in your reports, you could alter the stored procedures that generate th
e
reports to run in READ UNCOMMITTED isolation. That way, the report
transaction will not be blocked by any writers. If you can't run that risk,
you could replicate the database to another db, and use the replicated
(subscriber) db solely for reporting.
Or, migrate to SQL 2005 and use the new READ COMMITTED SNAPSHOT isolation
level ;-)
"mvp" wrote:

> Hello Everybody,
> We have arround 10 reports, i have written store procedures and i have
> created proper indexes on all the required columns. When i run specific
> report, it takes around 40 secondes but when around 20 users log in to the
> system (we have microsoft reporting server as a reporting tool and sql ser
ver
> 2000 as a DB)
> and start executing reports in that case the report which is taking around
> 40 seconds(when around couple of users log in to the system) takes around
10
> minutes to run.
> Here in all the reports we do only reads, no write operation and i do not
> have any transactions in any of report store procedure.
>
> I know when more than one person try to run same store procedure
> at the same time in that case all the underlying table get shared lock.
> I have checked DB Server CPU utilization, it is quiet normal.
>
> So clearly it looks like when many users log in to the system, report
> execution becomes very slow.
> so what are the possible causes ?
> Pls let me know.|||Hello Andrew/Mark,
Thanks for the reply. yes we do have report db is read only. We just load
data feed twice in a w in night time.
so how can i change it to read uncommited ?
So Should I add WITH NOLOCK after each table in my store procedure.. Or just
add
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before the start of all my
report procedures ?
Will this reduct access time of each report when multiple user will login to
system ?
Pls let me know
"Andrew J. Kelly" wrote:

> Low CPU utilization in a case like this usually suggests another bottlenec
k.
> Most likely disks or memory. If the reports are strictly read only and th
e
> data is not being changed you might want to consider using the Read
> Uncommitted transaction Isolation Level for the reports. It's not to reduc
e
> blocking since you shouldn't have any but more to reduce the number of loc
ks
> and free up resources associated with them. Here are some links that shoul
d
> help to find the bottlenecks:
>
> http://www.microsoft.com/sql/techin.../perftuning.asp
> Performance WP's
> http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
> http://www.sql-server-performance.c...mance_audit.asp
> hardware Performance CheckList
> http://www.sql-server-performance.c...rmance_tips.asp
> SQL 2000 Performance tuning tips
> http://www.support.microsoft.com/?id=q224587 Troubleshooting App
> Performance
> http://msdn.microsoft.com/library/d...
fmon_24u1.asp
> Disk Monitoring
> --
> Andrew J. Kelly SQL MVP
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:6688A51E-0414-4D52-B1C0-339F153ED13C@.microsoft.com...
>
>|||Is the db actually placed in READ_ONLY mode? If so then SQL Server will not
use locks anyway and most of that is moot. That would probably be the best
way to handle it anyway. If you only update it a few times a w then keep
it in Read_Only mode for all times other than when you update it. But I
still think you have a memory or disk issue as well. Please see the links I
posted to determine which one(s) you may have.
Andrew J. Kelly SQL MVP
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:F46DDE9A-99DA-476F-AF29-C00313190BBC@.microsoft.com...
> Hello Andrew/Mark,
> Thanks for the reply. yes we do have report db is read only. We just load
> data feed twice in a w in night time.
> so how can i change it to read uncommited ?
> So Should I add WITH NOLOCK after each table in my store procedure.. Or
> just
> add
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before the start of all
> my
> report procedures ?
> Will this reduct access time of each report when multiple user will login
> to
> system ?
> Pls let me know
>
> "Andrew J. Kelly" wrote:
>|||Hi Andres,
yes my db will be read only except between friday to suday.
so what should i do,
should i put WITH NOLOCK after each table in select statement of my report
store procedures ? or just add
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
at the top of the report store procedure ?
If i will do above thing
will it not put shared locks on the tables of procedures if multiple users
will execute it ?
Pls let me know.
Thx
Let me know.
"Andrew J. Kelly" wrote:

> Is the db actually placed in READ_ONLY mode? If so then SQL Server will n
ot
> use locks anyway and most of that is moot. That would probably be the bes
t
> way to handle it anyway. If you only update it a few times a w then ke
ep
> it in Read_Only mode for all times other than when you update it. But I
> still think you have a memory or disk issue as well. Please see the links
I
> posted to determine which one(s) you may have.
> --
> Andrew J. Kelly SQL MVP
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:F46DDE9A-99DA-476F-AF29-C00313190BBC@.microsoft.com...
>
>|||OK I am not sure we are talking the same thing here or not. When I say the
db is Read_only I mean you have actually done an ALTER DATABASE and set it
to read_only. That is different than just saying that no one will edit any
rows during. When the DB is put into this state it is truly READ_ONLY and
as such the engine knows that it does not require any locks since there is
no way the data will change. As such when you are in that state there is no
need to change the transaction level or to use NOLOCK. The whole purpose of
locking a row when you are editing it is so someone else doesn't edit that
same row at the same time. Also so someone doesn't read a value that is not
committed. When it is in READ_ONLY mode these conditions will never exist
so locking is not required.
But again, while this may help with memory utilization I don't think it is
the root cause of your issues. If there is no editing going on the reports
are using SHARED locks. That means there is no blocking. Please refer to
the links to get to the root cause.
Andrew J. Kelly SQL MVP
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:7E9A8245-DFE6-4237-A55A-200A3165335F@.microsoft.com...
> Hi Andres,
> yes my db will be read only except between friday to suday.
> so what should i do,
> should i put WITH NOLOCK after each table in select statement of my report
> store procedures ? or just add
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> at the top of the report store procedure ?
> If i will do above thing
> will it not put shared locks on the tables of procedures if multiple users
> will execute it ?
> Pls let me know.
> Thx
> Let me know.
> "Andrew J. Kelly" wrote:
>|||Hi Andrew,
Thanks for the reply.
So what i understand from your reply is that when database is in READ_ONLY
mode, still we can update/delete/insert rows into db but when we read, it
will not read only commited transaction, it may read dirty transactions too.
My Reports run during mon-fri and insert/update/delete happens during friday
night to sunday night.
So you are saying following thing,
If multiple users are attacking reports at same time during mon-fri and if i
put NOLOCK or SET TRANSACTION ISOLATION LEVEL UNCOMMITED, it will not help m
e
because in my case tables gets shared locks and they do not block read or
hurt performance, right ?
let me know if i misunderstand anything
"Andrew J. Kelly" wrote:

> OK I am not sure we are talking the same thing here or not. When I say th
e
> db is Read_only I mean you have actually done an ALTER DATABASE and set it
> to read_only. That is different than just saying that no one will edit an
y
> rows during. When the DB is put into this state it is truly READ_ONLY and
> as such the engine knows that it does not require any locks since there is
> no way the data will change. As such when you are in that state there is
no
> need to change the transaction level or to use NOLOCK. The whole purpose
of
> locking a row when you are editing it is so someone else doesn't edit that
> same row at the same time. Also so someone doesn't read a value that is no
t
> committed. When it is in READ_ONLY mode these conditions will never exist
> so locking is not required.
> But again, while this may help with memory utilization I don't think it is
> the root cause of your issues. If there is no editing going on the report
s
> are using SHARED locks. That means there is no blocking. Please refer to
> the links to get to the root cause.
> --
> Andrew J. Kelly SQL MVP
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:7E9A8245-DFE6-4237-A55A-200A3165335F@.microsoft.com...
>
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:573339
On Wed, 21 Dec 2005 14:49:02 -0800, mvp wrote:

>Hi Andrew,
>Thanks for the reply.
>So what i understand from your reply is that when database is in READ_ONLY
>mode, still we can update/delete/insert rows into db but when we read, it
>will not read only commited transaction, it may read dirty transactions too.[/color
]
Hi mvp,
No. If database is in READ_ONLY mode, all INSERT, UPDATE and DELETE
statements will fail. Only SELECT is permitted.
>My Reports run during mon-fri and insert/update/delete happens during frida
y
>night to sunday night.
>So you are saying following thing,
>If multiple users are attacking reports at same time during mon-fri and if
i
>put NOLOCK or SET TRANSACTION ISOLATION LEVEL UNCOMMITED, it will not help
me
>because in my case tables gets shared locks and they do not block read or
>hurt performance, right ?
No. If database is in READ_ONLY mode, SQL Server will not use any locks
at all. Adding NOLOCK or setting transaction level to read uncommited
has no effect at all, since no locks are taken anyway.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Performace penality when selecting on a numeric column in SQL 2005

In SQL 2000 SP4 there was introduced a performance penalty when doing select
on numeric columns (article: http://support.microsoft.com/kb/899976/en-us).
It was possible to revert to the behaviour before SP4 by running the server
with a trace flag (-T9059).
Now I'm trying to find out the behaviour in SQL 2005. Is the behaviour same
as in SQL 2000 SP4 where you get an index scan instead of index seek. Or have
they fixed this in some other way? I know that the trace flag does no longer
exist in 2005.
> Is the behaviour same as in SQL 2000 SP4 where you get an index
> scan instead of index seek.
Sorry, I mean vice versa ofcourse:
Is the behaviour same as in SQL 2000 SP4 where you get an index _seek_
instead of index _scan_?
|||I ran the "scenario 2" query from the article under SQL 2005 SP2 and got an
index seek instead of scan. It looks like the issue has been addressed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stefan Solender" <StefanSolender@.discussions.microsoft.com> wrote in
message news:5D27738A-4DAC-4EBF-9F2E-B4DAC489C65F@.microsoft.com...
> Sorry, I mean vice versa ofcourse:
> Is the behaviour same as in SQL 2000 SP4 where you get an index _seek_
> instead of index _scan_?
>
|||Yes I did the same test too and got index scan. But that does not have to
mean the there is no other performance penalty. I just want some final
statement that I can give our customers.

Performace penality when selecting on a numeric column in SQL 2005

In SQL 2000 SP4 there was introduced a performance penalty when doing select
on numeric columns (article: http://support.microsoft.com/kb/899976/en-us).
It was possible to revert to the behaviour before SP4 by running the server
with a trace flag (-T9059).
Now I'm trying to find out the behaviour in SQL 2005. Is the behaviour same
as in SQL 2000 SP4 where you get an index scan instead of index seek. Or have
they fixed this in some other way? I know that the trace flag does no longer
exist in 2005.> Is the behaviour same as in SQL 2000 SP4 where you get an index
> scan instead of index seek.
Sorry, I mean vice versa ofcourse:
Is the behaviour same as in SQL 2000 SP4 where you get an index _seek_
instead of index _scan_?|||I ran the "scenario 2" query from the article under SQL 2005 SP2 and got an
index seek instead of scan. It looks like the issue has been addressed.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stefan Solender" <StefanSolender@.discussions.microsoft.com> wrote in
message news:5D27738A-4DAC-4EBF-9F2E-B4DAC489C65F@.microsoft.com...
>> Is the behaviour same as in SQL 2000 SP4 where you get an index
>> scan instead of index seek.
> Sorry, I mean vice versa ofcourse:
> Is the behaviour same as in SQL 2000 SP4 where you get an index _seek_
> instead of index _scan_?
>|||Yes I did the same test too and got index scan. But that does not have to
mean the there is no other performance penalty. I just want some final
statement that I can give our customers.

Performace penality when selecting on a numeric column in SQL 2005

In SQL 2000 SP4 there was introduced a performance penalty when doing select
on numeric columns (article: http://support.microsoft.com/kb/899976/en-us).
It was possible to revert to the behaviour before SP4 by running the server
with a trace flag (-T9059).
Now I'm trying to find out the behaviour in SQL 2005. Is the behaviour same
as in SQL 2000 SP4 where you get an index scan instead of index seek. Or hav
e
they fixed this in some other way? I know that the trace flag does no longer
exist in 2005.> Is the behaviour same as in SQL 2000 SP4 where you get an index
> scan instead of index seek.
Sorry, I mean vice versa ofcourse:
Is the behaviour same as in SQL 2000 SP4 where you get an index _seek_
instead of index _scan_?|||I ran the "scenario 2" query from the article under SQL 2005 SP2 and got an
index seek instead of scan. It looks like the issue has been addressed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stefan Solender" <StefanSolender@.discussions.microsoft.com> wrote in
message news:5D27738A-4DAC-4EBF-9F2E-B4DAC489C65F@.microsoft.com...
> Sorry, I mean vice versa ofcourse:
> Is the behaviour same as in SQL 2000 SP4 where you get an index _seek_
> instead of index _scan_?
>

Percentage returning 0 values

Hi all

im trying to divide 2 columns but when i divide it equals 0. However if i add, subtract or multiple it works fine...

select #all.playerid, #won.wongames, #all.allgame, #won.wongames/#all.allgame as percentage
from #all
left join #won
on #all.playerid = #won.playerid

Thanks in advance.
MehulIm thinking it might be that it does not allow decimals... is there a way to overcome this??

Thanks

Percentage Calulation in RS

Hey all,
I need to calculate % bases on 2 columns (Col1 and Col2), and my result that
I want is in Col3(%). Please see the layout below. Is that possible?
Col1 Col2 Col3(%)
--
A 5 50%
B 2 20%
C 3 30%
D 0 0%
Total 10 100%
Thanks in advance!
Calculate Percentage> Col1 Col2 Col3(%)
> --
> A 5 (textbox2) 50%
> B 2 (textbox2) 20%
> C 3 (textbox2) 30%
> D 0 (textbox2) 0%
> Total 10(textbox34) 100%
Assuming you have Tabular report Design,
Expression in Col3 can be -
= Reportitems!textbox2.value/Reportitems!textbox34.value
and Format field with %
(My formula syntax can be wrong, use textbox reading syntax)