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

Tuesday, March 27, 2012

Database Blobs & Performance

Hi geniuses,

I have a core database question in MS Access and SQL Server.

Assume the following table in MS Access or SQL Server:

Employees table:

EmpID, Name, Hiredate, Photo;

and the following query:

"SELECT EmpID, Name, Hiredate FROM Employees;"

The question is, "does the blob object (Photo) affect the performance of the query even when not included in the select statement?"

I mean, will I gain some additional performance when separating the blob fields to another table?

Thanks for the answer.

You should ask this question in the ms - sql forums,

From my point of view it should not, and i do not have an idea of the implementation of any of the ms databases till now,

anyone has related notes please direct on this, looks interesting.

|||

if you don′t select the image / binary column there should be no performance issue.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Unless you have specifically created the table with the image/binary data 'in the row', it is NOT stored in the table.

sp_tableoption N'MyTable', 'large value types out of row', 'ON'

is the default. In the table, there is only a 'pointer' in the actual location.

In any case, NOT including the image/binary field in the SELECT statement will definintely enhance performance. But there will be little effect noticed by moving the image/binary pointer (16 bytes) to another table.

Database Blobs & Performance

Hi geniuses,

I have a core database question in MS Access and SQL Server.

Assume the following table in MS Access or SQL Server:

Employees table:

EmpID, Name, Hiredate, Photo;

and the following query:

"SELECT EmpID, Name, Hiredate FROM Employees;"

The question is, "does the blob object (Photo) affect the performance of the query even when not included in the select statement?"

I mean, will I gain some additional performance when separating the blob fields to another table?

Thanks for the answer.

You should ask this question in the ms - sql forums,

From my point of view it should not, and i do not have an idea of the implementation of any of the ms databases till now,

anyone has related notes please direct on this, looks interesting.

|||

if you don′t select the image / binary column there should be no performance issue.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Unless you have specifically created the table with the image/binary data 'in the row', it is NOT stored in the table.

sp_tableoption N'MyTable', 'large value types out of row', 'ON'

is the default. In the table, there is only a 'pointer' in the actual location.

In any case, NOT including the image/binary field in the SELECT statement will definintely enhance performance. But there will be little effect noticed by moving the image/binary pointer (16 bytes) to another table.

Database benchmark

We are in the process of moving our databases from one server to another and
want to know what are some best to capture some performance benchmarks of
existing systems and compare it with the migrated database on the new server
so that at the end of the day performace is at best similar or better on the
new hardware.
Thanks
Hi Hassan
The obvious ones are to use perfmon and SQL Profiler over a variety of
periods/tests and produce comparative graphs/figures. You may also want to
replay profiles as a backend test.
If you are using a tool such as load runner or Visual Studio Team Test they
will collect their own stats that could be use to give a front end view.
As you are moving to a new system you should get a baseline using SQLIOSim
to make sure there are no issues with the new environment and have values
that you can use to compare against for future tests.
John
"Hassan" wrote:

> We are in the process of moving our databases from one server to another and
> want to know what are some best to capture some performance benchmarks of
> existing systems and compare it with the migrated database on the new server
> so that at the end of the day performace is at best similar or better on the
> new hardware.
> Thanks
>
>

Database benchmark

We are in the process of moving our databases from one server to another and
want to know what are some best to capture some performance benchmarks of
existing systems and compare it with the migrated database on the new server
so that at the end of the day performace is at best similar or better on the
new hardware.
ThanksHi Hassan :)
The obvious ones are to use perfmon and SQL Profiler over a variety of
periods/tests and produce comparative graphs/figures. You may also want to
replay profiles as a backend test.
If you are using a tool such as load runner or Visual Studio Team Test they
will collect their own stats that could be use to give a front end view.
As you are moving to a new system you should get a baseline using SQLIOSim
to make sure there are no issues with the new environment and have values
that you can use to compare against for future tests.
John
"Hassan" wrote:
> We are in the process of moving our databases from one server to another and
> want to know what are some best to capture some performance benchmarks of
> existing systems and compare it with the migrated database on the new server
> so that at the end of the day performace is at best similar or better on the
> new hardware.
> Thanks
>
>sql

Database benchmark

We are in the process of moving our databases from one server to another and
want to know what are some best to capture some performance benchmarks of
existing systems and compare it with the migrated database on the new server
so that at the end of the day performace is at best similar or better on the
new hardware.
ThanksHi Hassan
The obvious ones are to use perfmon and SQL Profiler over a variety of
periods/tests and produce comparative graphs/figures. You may also want to
replay profiles as a backend test.
If you are using a tool such as load runner or Visual Studio Team Test they
will collect their own stats that could be use to give a front end view.
As you are moving to a new system you should get a baseline using SQLIOSim
to make sure there are no issues with the new environment and have values
that you can use to compare against for future tests.
John
"Hassan" wrote:

> We are in the process of moving our databases from one server to another a
nd
> want to know what are some best to capture some performance benchmarks of
> existing systems and compare it with the migrated database on the new serv
er
> so that at the end of the day performace is at best similar or better on t
he
> new hardware.
> Thanks
>
>

Wednesday, March 7, 2012

Database "corruption" causing terrible performance?

We have a database that has (until recently) exhibited great performance
[SQL Server 2000, Data (MDF) size = 50GB].
The schema has not changed for months, the only thing that changes is the
data.
The other day, performance of the web-site dropped to almost zero...30
seconds for a page to appear that normally would appear in milliseconds.
I first checked the schema by comparing it against a similar database. The
schema (indexes etc) were identical.
I then closed the website to all users except me. I switched on SQL
profiler and opened the site. As soon as a few queries hit the machine, the
CPU flat-lined at 100% and stayed there for a minute or two.
I ran DBCC SHOWCONTIG and CHECKDB. None of these showed anything untoward.
However, I ran a tool to completely rebuild every index (took most of the
day). After that, performance had not improved. I increased the size of
the data and log files for both this and the tempdb - no effect. I updated
the statistics etc, but again still to no avail. I considered defragmenting
the disc, but the log and data file were hardly fragmented at all. What
else to try?
In desperation, I created a brand new database with identical schema and
using DTS, copied all the data into this database from the original database
(took overnight). I am pleased to say that the web site now has blistering
performance once again!
OK - so what's wrong with my original database that the DBCC diagnostic
tools fail to show? Investigating further this morning, I found the
following:
When I right click on [newDB].[tableA] and select properties, it informs me
that there are 2365000 rows. Select count(*) ... agrees with this value.
However, if I do the same with [originalDB].[tableA] the properties tell me
that there are 0 (zero) rows, but select count(*) shows there to be 2365000
rows.
I don't know enough of the internals of SQL Server, but I'm guessing that
the code that informs the Properties page that there are zero rows might be
the same code that helps the Optimiser decide whether to use an index or
not.
If this is the case, then I'm hoping that there is a tool that I've
overlooked that can correct these statistics. If so, can someone let me
know what this tool is? Also, why would SQL Server get into this state (and
has it been fixed in 2005/2008)?
Thanks to everyone who reads this
Griff
Griff
Did you identify those long ruinning queries by Profiler, did not you? See
an execution plan of these queries, do they perfom table/index scan?
"Griff" <GriffithsJ_520@.htomail.com> wrote in message
news:%23PTxDaNiIHA.4320@.TK2MSFTNGP06.phx.gbl...
> We have a database that has (until recently) exhibited great performance
> [SQL Server 2000, Data (MDF) size = 50GB].
> The schema has not changed for months, the only thing that changes is the
> data.
> The other day, performance of the web-site dropped to almost zero...30
> seconds for a page to appear that normally would appear in milliseconds.
> I first checked the schema by comparing it against a similar database.
> The schema (indexes etc) were identical.
> I then closed the website to all users except me. I switched on SQL
> profiler and opened the site. As soon as a few queries hit the machine,
> the CPU flat-lined at 100% and stayed there for a minute or two.
> I ran DBCC SHOWCONTIG and CHECKDB. None of these showed anything
> untoward. However, I ran a tool to completely rebuild every index (took
> most of the day). After that, performance had not improved. I increased
> the size of the data and log files for both this and the tempdb - no
> effect. I updated the statistics etc, but again still to no avail. I
> considered defragmenting the disc, but the log and data file were hardly
> fragmented at all. What else to try?
> In desperation, I created a brand new database with identical schema and
> using DTS, copied all the data into this database from the original
> database (took overnight). I am pleased to say that the web site now has
> blistering performance once again!
> OK - so what's wrong with my original database that the DBCC diagnostic
> tools fail to show? Investigating further this morning, I found the
> following:
> When I right click on [newDB].[tableA] and select properties, it informs
> me that there are 2365000 rows. Select count(*) ... agrees with this
> value. However, if I do the same with [originalDB].[tableA] the properties
> tell me that there are 0 (zero) rows, but select count(*) shows there to
> be 2365000 rows.
> I don't know enough of the internals of SQL Server, but I'm guessing that
> the code that informs the Properties page that there are zero rows might
> be the same code that helps the Optimiser decide whether to use an index
> or not.
> If this is the case, then I'm hoping that there is a tool that I've
> overlooked that can correct these statistics. If so, can someone let me
> know what this tool is? Also, why would SQL Server get into this state
> (and has it been fixed in 2005/2008)?
> Thanks to everyone who reads this
> Griff
>
|||> Did you identify those long ruinning queries by Profiler, did not you?
> See an execution plan of these queries, do they perfom table/index scan?
A very good question. Sadly, the answer is "no" and unfortunately, the site
is now live again and I'm not allowed to perform such tests on this database
as it will degrade the live DB's performance. I'll have to copy this rather
large DB to our DEV area to perform such a test.
As I mentioned, TableA shows "zero rows" when one pulls up the properties
page. Where is this information held?
I looked in the sysindexes table at the rows value. From my understanding,
if there is a clustered index, then the true row count should be returned
and then this value used for all non-clustered indexes. Now, this table
does not have a clustered index. There are two "indexes". The first one is
a non-clustered index and the rows value = 0 (which is what I'd expect).
The second is a primary key and the rows value is not 0 (which is what the
MSDN documentation would lead me to expect) but instead 258067. However,
the ACTUAL row count is 2365232 [select count()) from ...], so where the
rows value came from I've really no idea.
I've another example from TableB. This DOES have a clustered index. The
rows value here is 6180637 which agrees with Count(*). However, there are 5
other non-clustered indexes which all have the same rows value of 6177790 -
MSDN leads me to expect that this should equal the rows value for the
clustered index, but evidently it doesn't. And yes, I had completely rebuilt
the indexes and then run 'update statistics TableB' command before running
the query on sysindexes.
Griff
|||Interesting (well, I guess that depends...)
When I run the command:
DBCC SHOW_STATISTICS (TABLEA, TableA_IndexName)
On the new database, it returns three data sets (using query analyser here)
and the following message:
Statistics for INDEX 'TableA_IndexName'.
(1 row(s) affected)
(2 row(s) affected)
(200 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
However...
When I run this on the original database, it returns NO data sets, just the
following message:
Statistics for INDEX 'TableA_IndexName'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Griff
|||Griff
Create a clustered index on TableA, and again run SQL Server Profile to see
what is going on the server.
"Griff" <GriffithsJ_520@.htomail.com> wrote in message
news:OusS1iOiIHA.5724@.TK2MSFTNGP05.phx.gbl...
> A very good question. Sadly, the answer is "no" and unfortunately, the
> site is now live again and I'm not allowed to perform such tests on this
> database as it will degrade the live DB's performance. I'll have to copy
> this rather large DB to our DEV area to perform such a test.
> As I mentioned, TableA shows "zero rows" when one pulls up the properties
> page. Where is this information held?
> I looked in the sysindexes table at the rows value. From my
> understanding, if there is a clustered index, then the true row count
> should be returned and then this value used for all non-clustered indexes.
> Now, this table does not have a clustered index. There are two "indexes".
> The first one is a non-clustered index and the rows value = 0 (which is
> what I'd expect). The second is a primary key and the rows value is not 0
> (which is what the MSDN documentation would lead me to expect) but instead
> 258067. However, the ACTUAL row count is 2365232 [select count()) from
> ...], so where the rows value came from I've really no idea.
> I've another example from TableB. This DOES have a clustered index. The
> rows value here is 6180637 which agrees with Count(*). However, there are
> 5 other non-clustered indexes which all have the same rows value of
> 6177790 - MSDN leads me to expect that this should equal the rows value
> for the clustered index, but evidently it doesn't. And yes, I had
> completely rebuilt the indexes and then run 'update statistics TableB'
> command before running the query on sysindexes.
> Griff
>

Database "corruption" causing terrible performance?

We have a database that has (until recently) exhibited great performance
[SQL Server 2000, Data (MDF) size = 50GB].
The schema has not changed for months, the only thing that changes is the
data.
The other day, performance of the web-site dropped to almost zero...30
seconds for a page to appear that normally would appear in milliseconds.
I first checked the schema by comparing it against a similar database. The
schema (indexes etc) were identical.
I then closed the website to all users except me. I switched on SQL
profiler and opened the site. As soon as a few queries hit the machine, the
CPU flat-lined at 100% and stayed there for a minute or two.
I ran DBCC SHOWCONTIG and CHECKDB. None of these showed anything untoward.
However, I ran a tool to completely rebuild every index (took most of the
day). After that, performance had not improved. I increased the size of
the data and log files for both this and the tempdb - no effect. I updated
the statistics etc, but again still to no avail. I considered defragmenting
the disc, but the log and data file were hardly fragmented at all. What
else to try?
In desperation, I created a brand new database with identical schema and
using DTS, copied all the data into this database from the original database
(took overnight). I am pleased to say that the web site now has blistering
performance once again!
OK - so what's wrong with my original database that the DBCC diagnostic
tools fail to show? Investigating further this morning, I found the
following:
When I right click on [newDB].[tableA] and select properties, it informs me
that there are 2365000 rows. Select count(*) ... agrees with this value.
However, if I do the same with [originalDB].[tableA] the properties tell me
that there are 0 (zero) rows, but select count(*) shows there to be 2365000
rows.
I don't know enough of the internals of SQL Server, but I'm guessing that
the code that informs the Properties page that there are zero rows might be
the same code that helps the Optimiser decide whether to use an index or
not.
If this is the case, then I'm hoping that there is a tool that I've
overlooked that can correct these statistics. If so, can someone let me
know what this tool is? Also, why would SQL Server get into this state (and
has it been fixed in 2005/2008)?
Thanks to everyone who reads this
GriffGriff
Did you identify those long ruinning queries by Profiler, did not you? See
an execution plan of these queries, do they perfom table/index scan?
"Griff" <GriffithsJ_520@.htomail.com> wrote in message
news:%23PTxDaNiIHA.4320@.TK2MSFTNGP06.phx.gbl...
> We have a database that has (until recently) exhibited great performance
> [SQL Server 2000, Data (MDF) size = 50GB].
> The schema has not changed for months, the only thing that changes is the
> data.
> The other day, performance of the web-site dropped to almost zero...30
> seconds for a page to appear that normally would appear in milliseconds.
> I first checked the schema by comparing it against a similar database.
> The schema (indexes etc) were identical.
> I then closed the website to all users except me. I switched on SQL
> profiler and opened the site. As soon as a few queries hit the machine,
> the CPU flat-lined at 100% and stayed there for a minute or two.
> I ran DBCC SHOWCONTIG and CHECKDB. None of these showed anything
> untoward. However, I ran a tool to completely rebuild every index (took
> most of the day). After that, performance had not improved. I increased
> the size of the data and log files for both this and the tempdb - no
> effect. I updated the statistics etc, but again still to no avail. I
> considered defragmenting the disc, but the log and data file were hardly
> fragmented at all. What else to try?
> In desperation, I created a brand new database with identical schema and
> using DTS, copied all the data into this database from the original
> database (took overnight). I am pleased to say that the web site now has
> blistering performance once again!
> OK - so what's wrong with my original database that the DBCC diagnostic
> tools fail to show? Investigating further this morning, I found the
> following:
> When I right click on [newDB].[tableA] and select properties, it informs
> me that there are 2365000 rows. Select count(*) ... agrees with this
> value. However, if I do the same with [originalDB].[tableA] the properties
> tell me that there are 0 (zero) rows, but select count(*) shows there to
> be 2365000 rows.
> I don't know enough of the internals of SQL Server, but I'm guessing that
> the code that informs the Properties page that there are zero rows might
> be the same code that helps the Optimiser decide whether to use an index
> or not.
> If this is the case, then I'm hoping that there is a tool that I've
> overlooked that can correct these statistics. If so, can someone let me
> know what this tool is? Also, why would SQL Server get into this state
> (and has it been fixed in 2005/2008)?
> Thanks to everyone who reads this
> Griff
>|||> Did you identify those long ruinning queries by Profiler, did not you?
> See an execution plan of these queries, do they perfom table/index scan?
A very good question. Sadly, the answer is "no" and unfortunately, the site
is now live again and I'm not allowed to perform such tests on this database
as it will degrade the live DB's performance. I'll have to copy this rather
large DB to our DEV area to perform such a test.
As I mentioned, TableA shows "zero rows" when one pulls up the properties
page. Where is this information held?
I looked in the sysindexes table at the rows value. From my understanding,
if there is a clustered index, then the true row count should be returned
and then this value used for all non-clustered indexes. Now, this table
does not have a clustered index. There are two "indexes". The first one is
a non-clustered index and the rows value = 0 (which is what I'd expect).
The second is a primary key and the rows value is not 0 (which is what the
MSDN documentation would lead me to expect) but instead 258067. However,
the ACTUAL row count is 2365232 [select count()) from ...], so where the
rows value came from I've really no idea.
I've another example from TableB. This DOES have a clustered index. The
rows value here is 6180637 which agrees with Count(*). However, there are 5
other non-clustered indexes which all have the same rows value of 6177790 -
MSDN leads me to expect that this should equal the rows value for the
clustered index, but evidently it doesn't. And yes, I had completely rebuilt
the indexes and then run 'update statistics TableB' command before running
the query on sysindexes.
Griff|||Interesting (well, I guess that depends...)
When I run the command:
DBCC SHOW_STATISTICS (TABLEA, TableA_IndexName)
On the new database, it returns three data sets (using query analyser here)
and the following message:
Statistics for INDEX 'TableA_IndexName'.
(1 row(s) affected)
(2 row(s) affected)
(200 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
However...
When I run this on the original database, it returns NO data sets, just the
following message:
Statistics for INDEX 'TableA_IndexName'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Griff|||Griff
Create a clustered index on TableA, and again run SQL Server Profile to see
what is going on the server.
"Griff" <GriffithsJ_520@.htomail.com> wrote in message
news:OusS1iOiIHA.5724@.TK2MSFTNGP05.phx.gbl...
>> Did you identify those long ruinning queries by Profiler, did not you?
>> See an execution plan of these queries, do they perfom table/index scan?
> A very good question. Sadly, the answer is "no" and unfortunately, the
> site is now live again and I'm not allowed to perform such tests on this
> database as it will degrade the live DB's performance. I'll have to copy
> this rather large DB to our DEV area to perform such a test.
> As I mentioned, TableA shows "zero rows" when one pulls up the properties
> page. Where is this information held?
> I looked in the sysindexes table at the rows value. From my
> understanding, if there is a clustered index, then the true row count
> should be returned and then this value used for all non-clustered indexes.
> Now, this table does not have a clustered index. There are two "indexes".
> The first one is a non-clustered index and the rows value = 0 (which is
> what I'd expect). The second is a primary key and the rows value is not 0
> (which is what the MSDN documentation would lead me to expect) but instead
> 258067. However, the ACTUAL row count is 2365232 [select count()) from
> ...], so where the rows value came from I've really no idea.
> I've another example from TableB. This DOES have a clustered index. The
> rows value here is 6180637 which agrees with Count(*). However, there are
> 5 other non-clustered indexes which all have the same rows value of
> 6177790 - MSDN leads me to expect that this should equal the rows value
> for the clustered index, but evidently it doesn't. And yes, I had
> completely rebuilt the indexes and then run 'update statistics TableB'
> command before running the query on sysindexes.
> Griff
>

Sunday, February 19, 2012

Data Type Question

I do a lot of work in my db apps with race timing. I use ADO with SQL Server or MSAccess. I have a hard time working with performance times (where 5:15 means a performance time of 5 minutes and 15 seconds, for instance). AS nearly as I can tell, the datetime data types refer to 'time-of-day' things which processes a little differently. As a result, when I submit this type of data I just drop it in as a string but this causes problems when doing comparison-types of things.

Can someone steer me towards a better way to work with this kind of data?

Thanks!!!varchar()|||store as seconds, so smallint

rudy|||...written code to convert to seconds when I need to process it and then convert back. I just thought there had to be an easier way.