Read/Write Performance

Hello,

We currently run sql 2005 server and also sql express in our dev environments. We use sql express as an offline store (smart client). We have a similar/exact schema on the sql 2005 server and also the express.

We use the auto attach feature to connect to the express version of the database. Both the developer machines and the one that is running the sql 2005 server have exactly the same hardware configuration. The only difference may be that the server box is not running the VS.Net environment. The disk space etc is pretty much the same. Actually we run another database server(DB2) on the 2005 server machine.

We have observed that sql express is much slower and queries execute much slower aswell. For example, this may not be a totally scientific way of checking but a long running query on the server took only 2 minutes while on express it took longer than 9 minutes. The schema and data etc are the same.

Is there something we need to look into as far as read write speed/performance goes ?

TIA,

Avinash

Hi Avinash,

Could you provide a bit more information about how you determined the time it took to run the queries? Understanding your testing methodology will help determine if it is contributing or not.

Additionally, you mention you're using the auto attach feature, are you using User Instances as well? When using the VS UI to create connection to a database, the connection string specifies User Instance = True. This should be fine, but it's important to know.

Regards,

Mike Wachal
SQL Express team

-
Please mark your thread as Answered when you get your solution.

|||

Hello Mike Wachal,

Yes we use User Instance and Auto Attach in the connection string to express.

At this time, I dont have a very 'scientific' or for that matter a very solid way to test out the performance. My question came from a general observation and thought I'd bounce it off the expert community to see if there was some caveats built into the use of express particularly with the auto attach mode.

Like I've already mentioned - our general observation is that queries 'Seem' to take longer on express both read and write when compared to 2005 server. Again, this may be a configuration thing aswell. But we are running the default configuration of express as done from within VS.Net 2005 setup and have made no changes what so ever.

On some counts we've put the start time and end time of execution in trace messages and have found the difference in execution times.

Thats all I have at this time,

Thanks and Regards,

Avinash

|||

Thanks Avinash,

In general, SQL Express should perform similarly to other Editions of SQL Server, but we do have limitation that might affect performance. For one thing, SQL Express will only use a single CPU and will only address 1 GB of RAM. If your hardware has more than this, then you could see a performance difference because the non-Express edition would be able to use the extra hardware components to increass performance over Express Edition.

If you're hardware is the same and within the limitations of SQL Express, I'm not sure what could be the issue without knowing more about the specific queries and data that is being queried.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

Hello Mike,

Many thanks for your response. In our case both the server and the client run the exact same hardware. They are (both) running on 1GB RAM.

I'll watch out for any further issues I may run into - and then bring them to your notice with all the data that I can provide.

Thanks,

Avinash

|||

OK Avinash,

Good luck with this.

As you find specific queries that perform differently you might want to bring those specific queries up in the SQL Database Engine forum. The folks in that forum will likely have some additional ideas both on tuning your queries and why they may perform differently on different Editions.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

sql

read only database performance question

In a nutshell, I have a stored proc that takes appx 15 minutes when it uses
a read-only database, and about 2 minutes when the database is not
read-only.

Details:

SQL Server 7

The stored proc exists on a writable database on the same server. It stuffs
a subset of about 20000 rows into a temp table and repeatedly updates the
temp table while doing selects and joins on the read only database, for
example,

Select *
into #LocalTempTable
from SAMESERVER.dbo.ReadOnlyDatabaseTable

Update tmp set tmp.SomeColumn = 1
from #LocalTempTable tmp
inner join SAMESERVER.dbo.ReadOnlyDatabaseTable x on
tmp.ID = x.ID
where SomeCriteria = SomeValue, etc, etc.

If I change the read-only database to writable it works fine. There are no
explicit transactions used in the stored proc. On the same server, a
production database exists and is constantly using transactions and temp
tables, but I wouldn't think this is an overall server performance issue
because it handles the same load when the suspect database is not read-only.

Any thoughts will be greatly appreciated. I'm searching all over and can't
seem to find reference to this type of issue."Bruce Hendry" <bruce@.binya.com> wrote in message
news:yNf2e.3954$oy3.2375@.newssvr30.news.prodigy.co m...
> In a nutshell, I have a stored proc that takes appx 15 minutes when it
> uses a read-only database, and about 2 minutes when the database is not
> read-only.
> Details:
> SQL Server 7
> The stored proc exists on a writable database on the same server. It
> stuffs a subset of about 20000 rows into a temp table and repeatedly
> updates the temp table while doing selects and joins on the read only
> database, for example,
> Select *
> into #LocalTempTable
> from SAMESERVER.dbo.ReadOnlyDatabaseTable
> Update tmp set tmp.SomeColumn = 1
> from #LocalTempTable tmp
> inner join SAMESERVER.dbo.ReadOnlyDatabaseTable x on
> tmp.ID = x.ID
> where SomeCriteria = SomeValue, etc, etc.
> If I change the read-only database to writable it works fine. There are
> no explicit transactions used in the stored proc. On the same server, a
> production database exists and is constantly using transactions and temp
> tables, but I wouldn't think this is an overall server performance issue
> because it handles the same load when the suspect database is not
> read-only.
> Any thoughts will be greatly appreciated. I'm searching all over and
> can't seem to find reference to this type of issue.

I don't know, but the usual advice would be to use Profiler and/or Query
Analyzer to trace the procedure execution and view the execution plan, to
see exactly what's taking so long.

To take a complete guess, in the writable database, MSSQL is adding
statistics dynamically to the table to improve performance. Although the
execution times are very different, and I'm not really convinced that
missing statistics could make such a difference. You might also put an index
on #LocalTempTable(ID) if you haven't already.

Simon

READ COMMITTED SNAPSHOT ON causes performance degradation

I am running a benchmark test with multiple connections running the same
stored procedure with different parameters. This stored procedures does only
SELECT. There are no other activity on the database.
The stored procedure containst this select
SELECT Model,AVG(Price),MIN(Price),MAX(Price),COUNT(*)
FROM SH_Product
WHERE Project_Number = @.Station
AND EmployeeID = 0
AND Type = @.Match100
GROUP BY Model
ORDER BY Model
When the database is set in READ COMMITTED SNAPSHOT OFF mode, the number of
transactions per second increases linearly as more and more connections are
added.
But when the database is set to READ COMMITTED SNAPSHOT ON, the performance
degrades after 20 users, the total transactions processed per second remains
constant when number of users increase. That means for each user the
transactions per second reduces.
I can understand this if there was any other INSERT/UPDATE/DELETE activity
happening on the database, as SELECT will have to traverse the row version
chain to get the data, but in SELECT only environment, how can the
performance degrade.
With READ COMMITTED SNAPSHOT ON, there are no locks to acquire hence less
overhead for SQL Server. I have a PSS ticket open for this, but I am getting
a satisfactory answer. All I get is since SELECT needs to go to tempdb to get
row version it is slower, but my point is if there is no data change why does
SQL Server has to go to tempdb?
Am I missing something?. Please help.
Thank youOn Thu, 27 Sep 2007 12:31:01 -0700, Shailesh Khanal wrote:
(snip)
>I can understand this if there was any other INSERT/UPDATE/DELETE activity
>happening on the database, as SELECT will have to traverse the row version
>chain to get the data, but in SELECT only environment, how can the
>performance degrade.
>With READ COMMITTED SNAPSHOT ON, there are no locks to acquire hence less
>overhead for SQL Server. I have a PSS ticket open for this, but I am getting
>a satisfactory answer. All I get is since SELECT needs to go to tempdb to get
>row version it is slower, but my point is if there is no data change why does
>SQL Server has to go to tempdb?
Hi Shailesh,
I'm not intimately familiar with the internals of READ COMMITTED
SNAPSHOT, but my guess is that SQL Server has to go to tempdb because it
can't know that there are no previous row versions there without looking
first.
Have you considered setting the database to READ ONLY? That will fully
eliminate all locking overhead.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Thanks Hugo
I am seeing this behavior while running a benchmark, which has different
sets of tests, one of them being CPU intensive test which only does SELECT.
It is not on a real production database so putting database in READ ONLY
mode is not an issue, but I wanted to understand the performance issue
without doing it.
I looked at page file structure in Kalen Delaney's book and I don't see any
information about whether SQL server puts a status bit on the page itself
for locked rows. But with READ COMMITTED SNAPSHOT ON, SQL server puts a 14
byte data in each row to store Transaction Sequence number (XSN), it is only
added when the row is updated. So logically speaking when a connection tries
to SELECT from a row, it has a XSN and when it goes to check the row in disk
if there is no XSN field then it should immediately know that the row is not
modified and should not go to tempdb to check.
Even if there is XSN for the row, and if it's value is less than SELECT XSN
then it should check lock records before going to tempdb. And this overhead
is also incurred when database is in READ COMMITTED SNAPSHOT OFF mode. So I
don't really get why the performance suffers so much.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:m3oqf31go4ti3d37736nsegqp2patoqjin@.4ax.com...
> On Thu, 27 Sep 2007 12:31:01 -0700, Shailesh Khanal wrote:
> (snip)
>>I can understand this if there was any other INSERT/UPDATE/DELETE activity
>>happening on the database, as SELECT will have to traverse the row version
>>chain to get the data, but in SELECT only environment, how can the
>>performance degrade.
>>With READ COMMITTED SNAPSHOT ON, there are no locks to acquire hence less
>>overhead for SQL Server. I have a PSS ticket open for this, but I am
>>getting
>>a satisfactory answer. All I get is since SELECT needs to go to tempdb to
>>get
>>row version it is slower, but my point is if there is no data change why
>>does
>>SQL Server has to go to tempdb?
> Hi Shailesh,
> I'm not intimately familiar with the internals of READ COMMITTED
> SNAPSHOT, but my guess is that SQL Server has to go to tempdb because it
> can't know that there are no previous row versions there without looking
> first.
> Have you considered setting the database to READ ONLY? That will fully
> eliminate all locking overhead.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

RE: Script task and OLEDB destination Performance

Hi fellows,

Sorry to disturb but just a question. I have a package which extracts all the records from table A and update to table B. These records may range from 100,000 to 500,000 records.

So my question is that whether is it more feasibile/efficient to use script task to pump all the rows into table B from table A or use OLEDB destionation using sql command. Which is more efficient and help me increase my package performance? Thanks again.

Regards,

Ken

If they are inserts, I'd use the OLEDB Destination to insert directly into the target table. If they are updates, use an OLEDB Destination to write the data to a temp table, then use a Execute SQL task after the data flow to issue a batch update.

RDO resultset performance problem

Hi to all,
I have a big performance problem with looping threw rdoresultset.
I made some very complex sql queries. They link like 5 tables and uses a
couple subqueries. The number of returned rows is something like 1000. Query
execution takes about 1 second, which is completly acceptable. But before I
can display result to the user, I need to perform some calculations over
returned rows. To loop threw resultset (movenext) takes 2 seconds, which is
VERY slow! The procedure is very optimized, so each value in resultset in
fetched only once. How can I access all data in the resultset in a much more
efficient way? I do not need to update any row, all I need to is to get
values from resultset.
Query is optimized for fast data access. I can not perform arithmetic
operations directly in query. The amount of data returned by the server is
something like 15kb, which is very small. I am using RDO and cursor library
is rdUseOdbc. For resultset I tryed to use different parameters, ie
readonly, forwardonly,... but performance does not change. And the server I
uses is SQL serer 2000.
Any idea would be really appreciated!!
Marko Erzen
Please note that RDO is deprecated so you might want to look into using
equivalent technologies in .NET or if you must do Native code, ADO.
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Uwa Agbonile[MSFT]
"Marko Erzen" <mare@.email.si> wrote in message
news:uNXiOSZbFHA.2440@.TK2MSFTNGP10.phx.gbl...
> Hi to all,
> I have a big performance problem with looping threw rdoresultset.
> I made some very complex sql queries. They link like 5 tables and uses a
> couple subqueries. The number of returned rows is something like 1000.
Query
> execution takes about 1 second, which is completly acceptable. But before
I
> can display result to the user, I need to perform some calculations over
> returned rows. To loop threw resultset (movenext) takes 2 seconds, which
is
> VERY slow! The procedure is very optimized, so each value in resultset in
> fetched only once. How can I access all data in the resultset in a much
more
> efficient way? I do not need to update any row, all I need to is to get
> values from resultset.
> Query is optimized for fast data access. I can not perform arithmetic
> operations directly in query. The amount of data returned by the server is
> something like 15kb, which is very small. I am using RDO and cursor
library
> is rdUseOdbc. For resultset I tryed to use different parameters, ie
> readonly, forwardonly,... but performance does not change. And the server
I
> uses is SQL serer 2000.
>
> Any idea would be really appreciated!!
> Marko Erzen
>

RDO resultset performance problem

Hi to all,
I have a big performance problem with looping threw rdoresultset.
I made some very complex sql queries. They link like 5 tables and uses a
couple subqueries. The number of returned rows is something like 1000. Query
execution takes about 1 second, which is completly acceptable. But before I
can display result to the user, I need to perform some calculations over
returned rows. To loop threw resultset (movenext) takes 2 seconds, which is
VERY slow! The procedure is very optimized, so each value in resultset in
fetched only once. How can I access all data in the resultset in a much more
efficient way? I do not need to update any row, all I need to is to get
values from resultset.
Query is optimized for fast data access. I can not perform arithmetic
operations directly in query. The amount of data returned by the server is
something like 15kb, which is very small. I am using RDO and cursor library
is rdUseOdbc. For resultset I tryed to use different parameters, ie
readonly, forwardonly,... but performance does not change. And the server I
uses is SQL serer 2000.
Any idea would be really appreciated!!
Marko ErzenPlease note that RDO is deprecated so you might want to look into using
equivalent technologies in .NET or if you must do Native code, ADO.
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Uwa Agbonile[MSFT]
"Marko Erzen" <mare@.email.si> wrote in message
news:uNXiOSZbFHA.2440@.TK2MSFTNGP10.phx.gbl...
> Hi to all,
> I have a big performance problem with looping threw rdoresultset.
> I made some very complex sql queries. They link like 5 tables and uses a
> couple subqueries. The number of returned rows is something like 1000.
Query
> execution takes about 1 second, which is completly acceptable. But before
I
> can display result to the user, I need to perform some calculations over
> returned rows. To loop threw resultset (movenext) takes 2 seconds, which
is
> VERY slow! The procedure is very optimized, so each value in resultset in
> fetched only once. How can I access all data in the resultset in a much
more
> efficient way? I do not need to update any row, all I need to is to get
> values from resultset.
> Query is optimized for fast data access. I can not perform arithmetic
> operations directly in query. The amount of data returned by the server is
> something like 15kb, which is very small. I am using RDO and cursor
library
> is rdUseOdbc. For resultset I tryed to use different parameters, ie
> readonly, forwardonly,... but performance does not change. And the server
I
> uses is SQL serer 2000.
>
> Any idea would be really appreciated!!
> Marko Erzen
>

RDO resultset performance problem

Hi to all,
I have a big performance problem with looping threw rdoresultset.
I made some very complex sql queries. They link like 5 tables and uses a
couple subqueries. The number of returned rows is something like 1000. Query
execution takes about 1 second, which is completly acceptable. But before I
can display result to the user, I need to perform some calculations over
returned rows. To loop threw resultset (movenext) takes 2 seconds, which is
VERY slow! The procedure is very optimized, so each value in resultset in
fetched only once. How can I access all data in the resultset in a much more
efficient way? I do not need to update any row, all I need to is to get
values from resultset.
Query is optimized for fast data access. I can not perform arithmetic
operations directly in query. The amount of data returned by the server is
something like 15kb, which is very small. I am using RDO and cursor library
is rdUseOdbc. For resultset I tryed to use different parameters, ie
readonly, forwardonly,... but performance does not change. And the server I
uses is SQL serer 2000.
Any idea would be really appreciated!!
Marko ErzenHi
As you don't show code then it is not possible to say if your evaluation
that the work can not be done in T-SQL is correct. You don't say how you are
connecting, but using OLEDB and not ODBC would help. If you have to use ODBC
makes sure that you are not using tracing on the ODBC connection. Also try
using rdUseServer instead of rdUseODBC. You may also want to see if ADO
performs better.
John
"Marko Erzen" wrote:

> Hi to all,
> I have a big performance problem with looping threw rdoresultset.
> I made some very complex sql queries. They link like 5 tables and uses a
> couple subqueries. The number of returned rows is something like 1000. Que
ry
> execution takes about 1 second, which is completly acceptable. But before
I
> can display result to the user, I need to perform some calculations over
> returned rows. To loop threw resultset (movenext) takes 2 seconds, which i
s
> VERY slow! The procedure is very optimized, so each value in resultset in
> fetched only once. How can I access all data in the resultset in a much mo
re
> efficient way? I do not need to update any row, all I need to is to get
> values from resultset.
> Query is optimized for fast data access. I can not perform arithmetic
> operations directly in query. The amount of data returned by the server is
> something like 15kb, which is very small. I am using RDO and cursor librar
y
> is rdUseOdbc. For resultset I tryed to use different parameters, ie
> readonly, forwardonly,... but performance does not change. And the server
I
> uses is SQL serer 2000.
>
> Any idea would be really appreciated!!
> Marko Erzen
>
>|||Hi
If I use the rdUseServer then query executes four times slower. But movenext
method is fast, very fast. The total duration of the query and local
processing is almost equal using the tdUseODBC. But in this case all other
queries in the application slows down too much. So rduseServer is not a good
option.
I also tryed using the ADO connection. The performance is almost the same as
using the RDO object. And I am not tracing queries.
Here is a chunk of the SQL in question:
SELECT am.datum, rtm.hotel_code, rtm.summary_code, rtm.room_type,
rtm.regular_rooms AS total_rooms, restm.definite,
ISNULL((SELECT SUM(ISNULL(re.nr_of_rooms,0)) FROM
reservations re,
reservation_type rest
WHERE re.reservat_type = rest.reservat_type AND
re.reservation_nr > 0 AND re.reservation_nr = re.group_res_nr AND
NOT (re.arrival < '2004-06-07' AND re.check_in = 0) AND
re.canceled_on IS NULL AND
((re.group_arrival <= '2004-07-02' AND re.group_departure > '2004-06-07') OR
(re.group_arrival = re.group_departure AND re.group_arrival = '2004-06-07'))
AND
re.group_arrival <= am.datum AND
re.group_departure > am.datum AND
rest.definite = restm.definite AND
re.hotel_code = rtm.hotel_code AND
re.summary_code= rtm.summary_code AND
re.room_type = rtm.room_type AND
am.datum BETWEEN '2004-06-07' AND '2004-07-02' AND
rest.definite IN (0) AND
re.hotel_code IN ('GHE','HH','HVP') ),0) AS occ_rooms ,
ISNULL((SELECT COUNT(DISTINCT roomstat_nr) FROM roomstat rs WHERE
rs.hotel_code = rtm.hotel_code AND
rs.summary_code = rtm.summary_code AND
rs.room_type = rtm.room_type AND
rs.fromdate <= am.datum AND
rs.todate > am.datum AND
rs.fromdate <= '2004-07-02' AND
rs.todate >= '2004-06-07' AND
am.datum BETWEEN '2004-06-07' AND '2004-07-02' AND
rs.hotel_code IN ('GHE','HH','HVP') ),0) AS oo
FROM avail am,
room_type rtm,
reservation_type restm WHERE
am.datum BETWEEN '2004-06-07' AND '2004-07-02' AND
rtm.regular < 5 AND
restm.definite IN (0) AND
rtm.hotel_code IN ('GHE','HH','HVP')
GROUP BY am.datum, rtm.hotel_code, rtm.summary_code, rtm.room_type,
rtm.regular_rooms, restm.definite
ORDER BY am.datum, rtm.hotel_code, rtm.summary_code, rtm.room_type,
restm.definite
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:B97359C8-E1C1-4207-AA51-AA846D1FB9C7@.microsoft.com...
> Hi
> As you don't show code then it is not possible to say if your evaluation
> that the work can not be done in T-SQL is correct. You don't say how you
are
> connecting, but using OLEDB and not ODBC would help. If you have to use
ODBC
> makes sure that you are not using tracing on the ODBC connection. Also try
> using rdUseServer instead of rdUseODBC. You may also want to see if ADO
> performs better.
> John
> "Marko Erzen" wrote:
>
Query
before I
is
in
more
is
library
server I|||Hi
SUM(ISNULL(re.nr_of_rooms,0)) is not needed, you may get a warning for
nulls but it would expect nr_of_rooms to be not null with a default of
0.
You may want to use derived tables for your sub-queries. This is
usually quicker for larger result sets.
I would prefer to use JOIN syntax as this may make it clearer.
Look at the query plan and possibly try the index tuning wizard.
John

Raw Partitions in SQL Server 2000

Hi,

I have a 3gb database (SQL 2k on Windows 2k) with some performance issues and it has been suggested to me to use Raw Partitions to increase performance. After researching this on BOL, I am hesitant to use raw partitions. I know this was a common practice in SQL 6.5. Has anyone tried this? What is the performance increase? Are there any other negatives besides what is mentioned in BOL? Any other insight would be greatly appreciated?

Thanks

MichaelMichael, I have used raw partitiones back with Ver 6.0 however controler technology has greatly improved since then. You need to read the little blurb in Books Online before you go down this path. I seriously doubt you will get enough of a performace boost to warrent the limitations raw partitions impose.

I would look instead to the drive configuration, channel allocation, transfer speed of the SCSI card, distribution of files across the drives etc.