Difference Between Physical IOs and Read-Ahead

Hello ...

I was running a table scan query on a 650MB table w/ 1 million rows. With statistics IO and time I noticed that I was able to get the following numbers on SQL Server 2005 (note this was the Sept CTP):

Logical IOs: 76,931

Physical IOs: 0

Read-Ahead: 58,321

CPU Time: 1212 ms

Clock Time: 42946 ms

So, in looking at the above I'm not seeing any physical IOs, but a lot of "read-ahead" IOs. If I understand it from the docs, a read-ahead essentially moves a data page into the cache. I understand this may mean getting a larger IO block size. Does this mean that I'm in fact doing physical IOs? This is a little confusing on the difference.

I noticed that when I ran a similar table scan query on a smaller table (say 266MB with 1 million rows) I had zero physical IOs and zero read-ahead hits. The query was significantly faster. The clock time was about 500 ms.

There were no indexes on either table.

Any advise here? Seems a little odd

Thanks!

DB

Read-ahead just means that the query processor asks for pages from a table to be pre-fetched into the cache. In this case, QP doesn't wait for the requests to complete - it is done asynchronously. The pages in the cache might be used later during the processing of the query at which point it may or may not be in the cache due to other activities on the server. So when the request for the page happens again it might incur in a physical IO (page was evicted from cache) or login IO (page is already in cache). It is possible that you see non-zero values for all the 3 counters. Physical IO is just that - fetching page from disk to memory. Logical IO is a page that is already in cache or memory (this can exceed the actual number of pages for a table if the same page is requested multiple times during query processing). You should think of read-ahead as an optimization mechanism that allows QP to request for pre-fetching pages that will be potentially used in the query.|||

Hi Umachandar ...

Sorry for the delay in responding to this...

From your message above and my real-life example above, since I have no physical IOs on the query using read aheads ... it sounds like the number of read ahead IOs is included in the total number of logical IOs. I think what you're saying is that in this situation w/ zero physical IOs, these are re-reads from cache of pages that had been previously cached ... is this the right way to look at it?

The example with the larger table using read aheads and the example with the smaller table and no read aheads were both just table scans. There were no indexes on either table, no where clause, and no physical IOs. I can see where scanning the larger table would take a bit longer than scanning the smaller table, but the clock time difference between the two samples seems really extreme. The larger table did require more CPU time (perhaps processing the read aheads ... ummm maybe re-reading previously cached pages?) but the overall picture doesn't make sense.

Are there cases where read aheads can perform poorly, and if so would one shut this feature off in terms of performance and tuning?

Thanks so much!

Doug

|||Yes, this is correct. The read-aheads are just requests to fetch pages from disk to cache and if they are already in memory then there is no additional work required. You should watch for cases where there was lot of read-ahead requests but the actual number of pages that were processed for the query is less in number. The difference in the CPU time might be due to the size of the larger table (i.e., more pages to read and process).

Difference Between Physical IOs and Read-Ahead

Hello ...

I was running a table scan query on a 650MB table w/ 1 million rows. With statistics IO and time I noticed that I was able to get the following numbers on SQL Server 2005 (note this was the Sept CTP):

Logical IOs: 76,931

Physical IOs: 0

Read-Ahead: 58,321

CPU Time: 1212 ms

Clock Time: 42946 ms

So, in looking at the above I'm not seeing any physical IOs, but a lot of "read-ahead" IOs. If I understand it from the docs, a read-ahead essentially moves a data page into the cache. I understand this may mean getting a larger IO block size. Does this mean that I'm in fact doing physical IOs? This is a little confusing on the difference.

I noticed that when I ran a similar table scan query on a smaller table (say 266MB with 1 million rows) I had zero physical IOs and zero read-ahead hits. The query was significantly faster. The clock time was about 500 ms.

There were no indexes on either table.

Any advise here? Seems a little odd

Thanks!

DB

Read-ahead just means that the query processor asks for pages from a table to be pre-fetched into the cache. In this case, QP doesn't wait for the requests to complete - it is done asynchronously. The pages in the cache might be used later during the processing of the query at which point it may or may not be in the cache due to other activities on the server. So when the request for the page happens again it might incur in a physical IO (page was evicted from cache) or login IO (page is already in cache). It is possible that you see non-zero values for all the 3 counters. Physical IO is just that - fetching page from disk to memory. Logical IO is a page that is already in cache or memory (this can exceed the actual number of pages for a table if the same page is requested multiple times during query processing). You should think of read-ahead as an optimization mechanism that allows QP to request for pre-fetching pages that will be potentially used in the query.|||

Hi Umachandar ...

Sorry for the delay in responding to this...

From your message above and my real-life example above, since I have no physical IOs on the query using read aheads ... it sounds like the number of read ahead IOs is included in the total number of logical IOs. I think what you're saying is that in this situation w/ zero physical IOs, these are re-reads from cache of pages that had been previously cached ... is this the right way to look at it?

The example with the larger table using read aheads and the example with the smaller table and no read aheads were both just table scans. There were no indexes on either table, no where clause, and no physical IOs. I can see where scanning the larger table would take a bit longer than scanning the smaller table, but the clock time difference between the two samples seems really extreme. The larger table did require more CPU time (perhaps processing the read aheads ... ummm maybe re-reading previously cached pages?) but the overall picture doesn't make sense.

Are there cases where read aheads can perform poorly, and if so would one shut this feature off in terms of performance and tuning?

Thanks so much!

Doug

|||Yes, this is correct. The read-aheads are just requests to fetch pages from disk to cache and if they are already in memory then there is no additional work required. You should watch for cases where there was lot of read-ahead requests but the actual number of pages that were processed for the query is less in number. The difference in the CPU time might be due to the size of the larger table (i.e., more pages to read and process).

Difference between dates...

Hi All...
What I would like to achieve:
Calculate the amount of time spent on a job (JDEID) per day. For eg:
Assigned id 110 has worked 9 Hrs, 35 minutes on 02/01/2005, 8hrs on
03/01/2005 etc.
This is how my query started:
SELECT Tbl_JMS_Manhours.JDEID, Tbl_MS_Employees.Name,
Tbl_JMS_Manhours.DateTimeStart, Tbl_JMS_Manhours.DateTimeEnd, DATEDIFF(hh,
Tbl_JMS_Manhours.DateTimeStart,
Tbl_JMS_Manhours.DateTimeEnd) AS Diff
FROM Tbl_JMS_Manhours LEFT OUTER JOIN
Tbl_MS_Employees ON Tbl_JMS_Manhours.AssignedID = Tbl_MS_Employees.EmployeeID
WHERE (Tbl_JMS_Manhours.JDEID = @.JDEID)
Currently my query is showing me the difference only in hours, but I want to
know the hrs and minutes spent on a job. Then I don't remember how to only
show the date (31/01/2005). If I can convert my general date to a short
date, then I can seperate the days.
This is some current sample info: (AssignedID = An employee id which is
linked to a name)
ID JDEID AssignedID DateTimeStart DateTimeEnd
24 12345 114 31/01/2005 13:13 31/01/2005 13:20
40 157837 110 02/02/2005 07:00 02/02/2005 16:19
41 157837 110 02/02/2005 17:34 02/02/2005 18:19
42 157837 110 03/02/2005 07:00 03/02/2005 16:19
43 157837 110 04/02/2005 17:34 04/02/2005 18:19
In the end I wanna see it something like:
JDEID AssignedID Date Worked
157837 110 02/02/2005 7:35
157837 110 03/02/2005 8.15
Or something like that.
Please any help..
ThanksLook up the convert function in Books on Line to see all of the date
formatting possibilites..I don't understand how converting the date to a
short date is going to help...
What I would do is to get the difference in minutes, then do a little math
to convert that to hours and minutes...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rudi Groenewald" <noone@.paflof.com> wrote in message
news:ctt85n$cpj$1@.ctb-nnrp2.saix.net...
> Hi All...
> What I would like to achieve:
> Calculate the amount of time spent on a job (JDEID) per day. For eg:
> Assigned id 110 has worked 9 Hrs, 35 minutes on 02/01/2005, 8hrs on
> 03/01/2005 etc.
> This is how my query started:
> SELECT Tbl_JMS_Manhours.JDEID, Tbl_MS_Employees.Name,
> Tbl_JMS_Manhours.DateTimeStart, Tbl_JMS_Manhours.DateTimeEnd, DATEDIFF(hh,
> Tbl_JMS_Manhours.DateTimeStart,
> Tbl_JMS_Manhours.DateTimeEnd) AS Diff
> FROM Tbl_JMS_Manhours LEFT OUTER JOIN
> Tbl_MS_Employees ON Tbl_JMS_Manhours.AssignedID => Tbl_MS_Employees.EmployeeID
> WHERE (Tbl_JMS_Manhours.JDEID = @.JDEID)
> Currently my query is showing me the difference only in hours, but I want
to
> know the hrs and minutes spent on a job. Then I don't remember how to
only
> show the date (31/01/2005). If I can convert my general date to a short
> date, then I can seperate the days.
> This is some current sample info: (AssignedID = An employee id which is
> linked to a name)
> ID JDEID AssignedID DateTimeStart DateTimeEnd
> 24 12345 114 31/01/2005 13:13 31/01/2005 13:20
> 40 157837 110 02/02/2005 07:00 02/02/2005 16:19
> 41 157837 110 02/02/2005 17:34 02/02/2005 18:19
> 42 157837 110 03/02/2005 07:00 03/02/2005 16:19
> 43 157837 110 04/02/2005 17:34 04/02/2005 18:19
>
> In the end I wanna see it something like:
> JDEID AssignedID Date Worked
> 157837 110 02/02/2005 7:35
> 157837 110 03/02/2005 8.15
>
> Or something like that.
> Please any help..
> Thanks
>|||convert function aint helpin much...
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:u59PnMfCFHA.2600@.TK2MSFTNGP09.phx.gbl...
> Look up the convert function in Books on Line to see all of the date
> formatting possibilites..I don't understand how converting the date to a
> short date is going to help...
> What I would do is to get the difference in minutes, then do a little math
> to convert that to hours and minutes...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Rudi Groenewald" <noone@.paflof.com> wrote in message
> news:ctt85n$cpj$1@.ctb-nnrp2.saix.net...
>> Hi All...
>> What I would like to achieve:
>> Calculate the amount of time spent on a job (JDEID) per day. For eg:
>> Assigned id 110 has worked 9 Hrs, 35 minutes on 02/01/2005, 8hrs on
>> 03/01/2005 etc.
>> This is how my query started:
>> SELECT Tbl_JMS_Manhours.JDEID, Tbl_MS_Employees.Name,
>> Tbl_JMS_Manhours.DateTimeStart, Tbl_JMS_Manhours.DateTimeEnd,
>> DATEDIFF(hh,
>> Tbl_JMS_Manhours.DateTimeStart,
>> Tbl_JMS_Manhours.DateTimeEnd) AS Diff
>> FROM Tbl_JMS_Manhours LEFT OUTER JOIN
>> Tbl_MS_Employees ON Tbl_JMS_Manhours.AssignedID =>> Tbl_MS_Employees.EmployeeID
>> WHERE (Tbl_JMS_Manhours.JDEID = @.JDEID)
>> Currently my query is showing me the difference only in hours, but I want
> to
>> know the hrs and minutes spent on a job. Then I don't remember how to
> only
>> show the date (31/01/2005). If I can convert my general date to a short
>> date, then I can seperate the days.
>> This is some current sample info: (AssignedID = An employee id which is
>> linked to a name)
>> ID JDEID AssignedID DateTimeStart DateTimeEnd
>> 24 12345 114 31/01/2005 13:13 31/01/2005 13:20
>> 40 157837 110 02/02/2005 07:00 02/02/2005 16:19
>> 41 157837 110 02/02/2005 17:34 02/02/2005 18:19
>> 42 157837 110 03/02/2005 07:00 03/02/2005 16:19
>> 43 157837 110 04/02/2005 17:34 04/02/2005 18:19
>>
>> In the end I wanna see it something like:
>> JDEID AssignedID Date Worked
>> 157837 110 02/02/2005 7:35
>> 157837 110 03/02/2005 8.15
>>
>> Or something like that.
>> Please any help..
>> Thanks
>>
>|||Try something like this:
SELECT Tbl_JMS_Manhours.JDEID, Tbl_MS_Employees.Name,
convert(varchar,Tbl_JMS_Manhours.DateTimeStart,101) as DateWorked,
DATEDIFF(hh, Tbl_JMS_Manhours.DateTimeStart, Tbl_JMS_Manhours.DateTimeEnd) +
':' +
datediff(mi, Tbl_JMS_Manhours.DateTimeStart, Tbl_JMS_Manhours.DateTimeEnd)
AS TimeWorked
FROM Tbl_JMS_Manhours LEFT OUTER JOIN
Tbl_MS_Employees ON Tbl_JMS_Manhours.AssignedID =Tbl_MS_Employees.EmployeeID
WHERE (Tbl_JMS_Manhours.JDEID = @.JDEID)
Vipul
"Rudi Groenewald" wrote:
> convert function aint helpin much...
>
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:u59PnMfCFHA.2600@.TK2MSFTNGP09.phx.gbl...
> > Look up the convert function in Books on Line to see all of the date
> > formatting possibilites..I don't understand how converting the date to a
> > short date is going to help...
> >
> > What I would do is to get the difference in minutes, then do a little math
> > to convert that to hours and minutes...
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Rudi Groenewald" <noone@.paflof.com> wrote in message
> > news:ctt85n$cpj$1@.ctb-nnrp2.saix.net...
> >> Hi All...
> >>
> >> What I would like to achieve:
> >>
> >> Calculate the amount of time spent on a job (JDEID) per day. For eg:
> >> Assigned id 110 has worked 9 Hrs, 35 minutes on 02/01/2005, 8hrs on
> >> 03/01/2005 etc.
> >>
> >> This is how my query started:
> >>
> >> SELECT Tbl_JMS_Manhours.JDEID, Tbl_MS_Employees.Name,
> >> Tbl_JMS_Manhours.DateTimeStart, Tbl_JMS_Manhours.DateTimeEnd,
> >> DATEDIFF(hh,
> >> Tbl_JMS_Manhours.DateTimeStart,
> >> Tbl_JMS_Manhours.DateTimeEnd) AS Diff
> >> FROM Tbl_JMS_Manhours LEFT OUTER JOIN
> >> Tbl_MS_Employees ON Tbl_JMS_Manhours.AssignedID => >> Tbl_MS_Employees.EmployeeID
> >> WHERE (Tbl_JMS_Manhours.JDEID = @.JDEID)
> >>
> >> Currently my query is showing me the difference only in hours, but I want
> > to
> >> know the hrs and minutes spent on a job. Then I don't remember how to
> > only
> >> show the date (31/01/2005). If I can convert my general date to a short
> >> date, then I can seperate the days.
> >>
> >> This is some current sample info: (AssignedID = An employee id which is
> >> linked to a name)
> >> ID JDEID AssignedID DateTimeStart DateTimeEnd
> >> 24 12345 114 31/01/2005 13:13 31/01/2005 13:20
> >> 40 157837 110 02/02/2005 07:00 02/02/2005 16:19
> >> 41 157837 110 02/02/2005 17:34 02/02/2005 18:19
> >> 42 157837 110 03/02/2005 07:00 03/02/2005 16:19
> >> 43 157837 110 04/02/2005 17:34 04/02/2005 18:19
> >>
> >>
> >> In the end I wanna see it something like:
> >>
> >> JDEID AssignedID Date Worked
> >> 157837 110 02/02/2005 7:35
> >> 157837 110 03/02/2005 8.15
> >>
> >>
> >> Or something like that.
> >>
> >> Please any help..
> >>
> >> Thanks
> >>
> >>
> >
> >
>
>

Diff between xxx_data.mdf and xxx.mdf files

What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I create
d, attach and detach database manytimes. SOme time its shows the database fi
le name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file n
ame as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows da
tabase na as "xxx".
I just want to know difference these file name conventions, what was the log
ic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004I don't think there is any specific logic behind the file names really, it's
just what the naming convention was at the time the databases were created.
The actual database name is independent of the file name (and a database ca
n consist of multiple files anyway, so it wouldn't make much sense to name t
he files after the database).
--
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3
988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I create
d, attach and detach database manytimes. SOme time its shows the database fi
le name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file n
ame as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows da
tabase na as "xxx".
I just want to know difference these file name conventions, what was the log
ic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004

Diff between xxx_data.mdf and xxx.mdf files

What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
I don't think there is any specific logic behind the file names really, it's just what the naming convention was at the time the databases were created. The actual database name is independent of the file name (and a database can consist of multiple files anyway, so it wouldn't make much sense to name the files after the database).
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004

Diff between xxx_data.mdf and xxx.mdf files

What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
There is no special significance to the suffixes _data and _log. They are
just the naming conventions used when the database was created. You can name
database files as anything you like, however the standard extensions for SQL
Server files are .MDF for data files and .LDF for log files.
MDB is the standard name for Access databases and isn't normally used for
SQL Server files.
(I've ignored your irrelevant cross-postings. Please don't cross-post. This
clearly has nothing to do with DTS, Clients or Connections so why post
there?)
David Portas
SQL Server MVP
|||I don't think there is any specific logic behind the file names really, it's just what the naming convention was at the time the databases were created. The actual database name is independent of the file name (and a database can consist of multiple files anyway, so it wouldn't make much sense to name the files after the database).
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
sql

Diff between xxx_data.mdf and xxx.mdf files

What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
I don't think there is any specific logic behind the file names really, it's just what the naming convention was at the time the databases were created. The actual database name is independent of the file name (and a database can consist of multiple files anyway, so it wouldn't make much sense to name the files after the database).
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004

Diff between xxx_data.mdf and xxx.mdf files

What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I create
d, attach and detach database manytimes. SOme time its shows the database fi
le name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file n
ame as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows da
tabase na as "xxx".
I just want to know difference these file name conventions, what was the log
ic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004There is no special significance to the suffixes _data and _log. They are
just the naming conventions used when the database was created. You can name
database files as anything you like, however the standard extensions for SQL
Server files are .MDF for data files and .LDF for log files.
MDB is the standard name for Access databases and isn't normally used for
SQL Server files.
(I've ignored your irrelevant cross-postings. Please don't cross-post. This
clearly has nothing to do with DTS, Clients or Connections so why post
there?)
David Portas
SQL Server MVP
--|||I don't think there is any specific logic behind the file names really, it's
just what the naming convention was at the time the databases were created.
The actual database name is independent of the file name (and a database ca
n consist of multiple files anyway, so it wouldn't make much sense to name t
he files after the database).
--
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3
988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I create
d, attach and detach database manytimes. SOme time its shows the database fi
le name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file n
ame as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows da
tabase na as "xxx".
I just want to know difference these file name conventions, what was the log
ic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004

diff between CPU time and elapsed time?

Hi,
What is the difference between the CPU time and the Elapsed time as
seen in the TKPROF results?
Thanks,
SantoshI don't know what TKPROF is, but:
CPU time is the time that the CPU was doing job for you. Elapse time is the
amount of time from
start of processing to end of processing. During this period, the CPU could
have done things for
somebody else.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<j.santosh.shenoy@.gmail.com> wrote in message
news:1135847807.311256.275360@.g44g2000cwa.googlegroups.com...
> Hi,
> What is the difference between the CPU time and the Elapsed time as
> seen in the TKPROF results?
> Thanks,
> Santosh
>|||> What is the difference between the CPU time and the Elapsed time
CPU time is how much time the CPU spent actually doing work. Elapsed time
is total time... not all of your time was necessarily spent doing CPU work,
it could have been waiting on other tasks, it could have spent time in I/O
(reading/writing to disk) or bandwidth...

> as seen in the TKPROF results?
What on earth is TKPROF? (I don't really need to know the answer, that was
bordering on rhetorical...)|||Hi Santosh
Although the explainations posted by others are probably still valid, you
may want to post this to an Oracle newsgroup!
John
<j.santosh.shenoy@.gmail.com> wrote in message
news:1135847807.311256.275360@.g44g2000cwa.googlegroups.com...
> Hi,
> What is the difference between the CPU time and the Elapsed time as
> seen in the TKPROF results?
> Thanks,
> Santosh
>sql

Did I just trip over a golden egg?

I'v been scouring the MSDN library and asking in newsgroups, forums, etc. on a way to extract [only] the time from a DateTime field (for a stored procedure) and no one could answer my question. then I tried this in QA:

selectright(getdate(),7)

and I found an answer. I'm now looking for someone to prove this is either a golden egg, a goose egg, or a documented feature I missed and if so where, so I can learn from it.

Jon

This will work,if you know what the default locale of the Sql Server is set as. However, if you don't, then you could get a nasty shock. For instance, if the server happened to be using ISO8601 dateformats then you would get something meaningless back.

Try

SELECT Right(CONVERT(varchar,GetDate(),126),7)

to see what I mean. The safest way to do what you are doing would be:

SELECT LTRIM(Right(CONVERT(varchar,GetDate(),0),7))

That should guarantee you are getting the correct dateformat string to work with. There's nothing mysterious about the RIGHT function, either - most SQL languages have a substring function.|||

Thank you and I accept the safest way as you suggested. The RIGHT function wasn't mysterious to me but rather the tool I used to get the answer I wanted. I was looking for an easy means to retrieve the time from SQL and was frustrated nobody out there could answer a question I thought would be a standard novice question. I'm realizing the simplest things are the cause of the greatest frustration in the programming world. Thanks for your solution.

Jon

|||Actually, I would suggest this:
SELECT CONVERT(char(12),GETDATE(),114)

For this datetime value:
2005-12-17 20:55:27.060

This would be returned:
20:55:27:060

SeeCAST and CONVERT for more information on how to use these 2 functions.|||

Thank You Terri! I was not familiar with CONVERT and now it seems ALL my date problems can be solved by this new information.

Jon

|||

Hmmm... that's interesting. To tell you the truth, I would have just used a whole bunch of datepart functions myself :)

Development tool for first time developer?

I am about to start developing a web app using MS SQL Server and was wondering whether there is any tool that I can use to test queries towards a remote server?

Something like a command line or an easy way I can write queries and run them straight away through the server and check results? It is my first time developing in SQL so I need something that allows me to play around with queries in the server and check results to quickly get a hang os MS SQL Server syntax, stored procedures and such.

Best Regards

Alethenorio

From the nature of your question, I assume that you are using SQL Server Express.

Yes, you can download the SQL Server Management Studio Express client tools for working with SQL Server Express.

SSMSE and other resources are available from these links:

SQL Server 2005 Express Edition (Advanced/SSMS/BI Toolkit)
http://tinyurl.com/yelwr9 (SSMSE)

SQL Server 2005 SSMS Express Tutorial
http://tinyurl.com/nqsca

SQL Server 2005 Express Books Online Express Edition
http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F

SQL Server 2005 Express Video Learning
http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx#1

|||

Thanks Arnie.

To be honest I am doing a project for a company and I have not yet got access to the web hotel but if express is the lower version of microsoft's SQl server I believe it is not the version they run here. They probably run the full capable version (Whatever that is).

Would you suggest the same tool for such or is there another tool better fit to handle higher versions of SQl server?

Regards

Alex

|||

You may wish to consider getting SQL Server Express for your own computer, and the client tools, for your learning process.

The version of SQL Server Management Studio (SSMSE) that works with SQL Express is very similar to the version provided with the 'paid' Editions.

Good luck as you get up to speed. I think you will enjoy learning about SQL Server.

Development Problems

Hi
Hope someone can help. I have a few legacy databases running on SQL Server
(developed before my time) which have a # in the name of the database.
I have been trying to write some reports that run off these databases
however whenever I attempt to preview or deploy the report I get an error
"Unable to access '/c'". I have narrowed it down to being something to do
with the # sign through moving things onto a test rig and removing the #
from the DB names and all is fine, however this is not really an option on
the live system.
My question is, does anyone know a way that I can get round this problem in
Reporting Services so I dont have to go through the process of modifying
about 20 different systems?
Thanks in advance.You could try placing the dbname within [], as in [pubs]
The brackets should take away any special meaning.
"Steven Clark" <sjc@.nospam.uk-support.net> wrote in message
news:hOOdnWLBBO7bvXHcRVnyrQ@.eclipse.net.uk...
> Hi
> Hope someone can help. I have a few legacy databases running on SQL
> Server (developed before my time) which have a # in the name of the
> database.
> I have been trying to write some reports that run off these databases
> however whenever I attempt to preview or deploy the report I get an error
> "Unable to access '/c'". I have narrowed it down to being something to do
> with the # sign through moving things onto a test rig and removing the #
> from the DB names and all is fine, however this is not really an option on
> the live system.
> My question is, does anyone know a way that I can get round this problem
> in Reporting Services so I dont have to go through the process of
> modifying about 20 different systems?
> Thanks in advance.
>

Developing for 2000 and 2005

Hi,
What are our options for developing for 2000 and 2005 "at the same time" /
on one computer.
I upgraded my local sql2000 to sql2005 and installed Visual Studio 2005 side
by side with Visual Studio 2003. Now Visual Studio 2003 cannot open Sql2000
report server projects anymore and 2005 "complains" about upgrading the
report projects. For several customers we still need to develop Sql2000
reports while other customers request Sql2005 reports.
Please advise,
ErikYou need to keep the source for the RS 2000 reports separate from the RS
2005 reports. RS 2005 will still run 2000 reports. 2000 will not run 2005
reports. 2005 Report Designer converts 2000 reports when you try to modify
them in 2005.
I took a 2000 report (I have both report designers on my machine) and I
deployed it to RS 2005.
Although 2005 is supposed to run 2000 reports I have noticed some minor
problems. For instance, if I do this:
= "Myparam1 = " & Parameters!Myparam1.Label
It doesn't like the use of label. However, if I convert the report by
bringing it into the 2005 designer, convert, redeploy the error goes away.
My suggestion is to have two report servers. One for 2000 and one for 2005.
Having both designers on one machine is not a problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Erik Tamminga" <agnimmate@.muimetca.ln_revese_the_previous> wrote in message
news:eEZs$fB7FHA.3660@.TK2MSFTNGP09.phx.gbl...
> Hi,
> What are our options for developing for 2000 and 2005 "at the same time" /
> on one computer.
> I upgraded my local sql2000 to sql2005 and installed Visual Studio 2005
> side by side with Visual Studio 2003. Now Visual Studio 2003 cannot open
> Sql2000 report server projects anymore and 2005 "complains" about
> upgrading the report projects. For several customers we still need to
> develop Sql2000 reports while other customers request Sql2005 reports.
> Please advise,
> Erik
>|||Plausible.
The upgrade of Sql2000 to Sql2005 (developer editions) on my own machine
removed Sql 2000 Report Designed and installed Sql 2005 Report Designed. I
now reinstalled the Sql 2000 Report Designed (which I didn't think was
possible).
Thanks.
Erik
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eQND1pE7FHA.268@.TK2MSFTNGP10.phx.gbl...
> You need to keep the source for the RS 2000 reports separate from the RS
> 2005 reports. RS 2005 will still run 2000 reports. 2000 will not run 2005
> reports. 2005 Report Designer converts 2000 reports when you try to modify
> them in 2005.
> I took a 2000 report (I have both report designers on my machine) and I
> deployed it to RS 2005.
> Although 2005 is supposed to run 2000 reports I have noticed some minor
> problems. For instance, if I do this:
> = "Myparam1 = " & Parameters!Myparam1.Label
> It doesn't like the use of label. However, if I convert the report by
> bringing it into the 2005 designer, convert, redeploy the error goes away.
> My suggestion is to have two report servers. One for 2000 and one for
> 2005. Having both designers on one machine is not a problem.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Erik Tamminga" <agnimmate@.muimetca.ln_revese_the_previous> wrote in
> message news:eEZs$fB7FHA.3660@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> What are our options for developing for 2000 and 2005 "at the same time"
>> / on one computer.
>> I upgraded my local sql2000 to sql2005 and installed Visual Studio 2005
>> side by side with Visual Studio 2003. Now Visual Studio 2003 cannot open
>> Sql2000 report server projects anymore and 2005 "complains" about
>> upgrading the report projects. For several customers we still need to
>> develop Sql2000 reports while other customers request Sql2005 reports.
>> Please advise,
>> Erik
>

Developer Edition Restrictions

Are there any restrictions on the Developer Edition of SQL Server 2000? I
am aware that you are allowed only 2 users in at the same time, but are
there other restrictions, like restrictions on the # of concurrent
transactions, or anything else like that? Or is it fully functional,
exactly the same as a regular version, other than the user limitation? I
have it installed on my development machine, and I'm using ASP to develop a
web application, and just wondered if this would be sufficient for running
a web application where potentially 25 or 30 users could be on the web site
at the same time (all using the same user name to connect, so it doesn't
violate the user limit).
Just wondering.
Thanks,
Jesse
"Jesse" <nospam@.fake.com> wrote in message
news:unrELl3OFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Are there any restrictions on the Developer Edition of SQL Server 2000? I
> am aware that you are allowed only 2 users in at the same time, but are
> there other restrictions, like restrictions on the # of concurrent
> transactions, or anything else like that? Or is it fully functional,
> exactly the same as a regular version, other than the user limitation? I
> have it installed on my development machine, and I'm using ASP to develop
> a web application, and just wondered if this would be sufficient for
> running a web application where potentially 25 or 30 users could be on the
> web site at the same time (all using the same user name to connect, so it
> doesn't violate the user limit).
>
There are no functional limitations on Developer Edition. It runs exactly
like Enterprise Edition.
The limitations are only in the license, which is here:
http://www.microsoft.com/sql/howtobuy/development.asp
There is no limit on the number of clients who can connect, but they can
only connect in conjunction with "designing, developing and testing" your
application. So you can test your application with 5000 users. But you
can't run it in production at all.
David

Developer Edition Restrictions

Are there any restrictions on the Developer Edition of SQL Server 2000? I
am aware that you are allowed only 2 users in at the same time, but are
there other restrictions, like restrictions on the # of concurrent
transactions, or anything else like that? Or is it fully functional,
exactly the same as a regular version, other than the user limitation? I
have it installed on my development machine, and I'm using ASP to develop a
web application, and just wondered if this would be sufficient for running
a web application where potentially 25 or 30 users could be on the web site
at the same time (all using the same user name to connect, so it doesn't
violate the user limit).
Just wondering.
Thanks,
Jesse"Jesse" <nospam@.fake.com> wrote in message
news:unrELl3OFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Are there any restrictions on the Developer Edition of SQL Server 2000? I
> am aware that you are allowed only 2 users in at the same time, but are
> there other restrictions, like restrictions on the # of concurrent
> transactions, or anything else like that? Or is it fully functional,
> exactly the same as a regular version, other than the user limitation? I
> have it installed on my development machine, and I'm using ASP to develop
> a web application, and just wondered if this would be sufficient for
> running a web application where potentially 25 or 30 users could be on the
> web site at the same time (all using the same user name to connect, so it
> doesn't violate the user limit).
>
There are no functional limitations on Developer Edition. It runs exactly
like Enterprise Edition.
The limitations are only in the license, which is here:
http://www.microsoft.com/sql/howtobuy/development.asp
There is no limit on the number of clients who can connect, but they can
only connect in conjunction with "designing, developing and testing" your
application. So you can test your application with 5000 users. But you
can't run it in production at all.
David

Developer Edition Restrictions

Are there any restrictions on the Developer Edition of SQL Server 2000? I
am aware that you are allowed only 2 users in at the same time, but are
there other restrictions, like restrictions on the # of concurrent
transactions, or anything else like that? Or is it fully functional,
exactly the same as a regular version, other than the user limitation? I
have it installed on my development machine, and I'm using ASP to develop a
web application, and just wondered if this would be sufficient for running
a web application where potentially 25 or 30 users could be on the web site
at the same time (all using the same user name to connect, so it doesn't
violate the user limit).
Just wondering.
Thanks,
Jesse"Jesse" <nospam@.fake.com> wrote in message
news:unrELl3OFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Are there any restrictions on the Developer Edition of SQL Server 2000? I
> am aware that you are allowed only 2 users in at the same time, but are
> there other restrictions, like restrictions on the # of concurrent
> transactions, or anything else like that? Or is it fully functional,
> exactly the same as a regular version, other than the user limitation? I
> have it installed on my development machine, and I'm using ASP to develop
> a web application, and just wondered if this would be sufficient for
> running a web application where potentially 25 or 30 users could be on the
> web site at the same time (all using the same user name to connect, so it
> doesn't violate the user limit).
>
There are no functional limitations on Developer Edition. It runs exactly
like Enterprise Edition.
The limitations are only in the license, which is here:
http://www.microsoft.com/sql/howtobuy/development.asp
There is no limit on the number of clients who can connect, but they can
only connect in conjunction with "designing, developing and testing" your
application. So you can test your application with 5000 users. But you
can't run it in production at all.
David

Determining the cause of tempdb growth?

I'm trying to determine the cause of tempdb to grow from ~ 30MB to well over
40GB. I have a SQL Profiler trace that was run at the time of this growth
but I don't see any particular SQL statements that consumed a lot of CPU or
performed a lot of reads (maybe the maximum was 50000 reads).
Any ideas as to what I could do to identify this problem?
Thanks in advance.Creation of temp tables and ORDER BY statements can cause tempdb growth. = Tempdb may also be used by other (SQL Server internal) query =optimization processes.
With that said, 40GB does sound rather large. How large are your databases?
What type of operations were being performed during the growth?
-- Keith
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message =news:OPLVdaYmDHA.1800@.TK2MSFTNGP10.phx.gbl...
> I'm trying to determine the cause of tempdb to grow from ~ 30MB to =well over
> 40GB. I have a SQL Profiler trace that was run at the time of this =growth
> but I don't see any particular SQL statements that consumed a lot of =CPU or
> performed a lot of reads (maybe the maximum was 50000 reads).
> > Any ideas as to what I could do to identify this problem?
> > Thanks in advance.
> >

Determining runtime or design time during Validate - workarounds?

I've seen a couple of posts in this forum on this subject. If anyone knows of a workaround it would be great to hear.

The problem is this. I'm writing a component that looks a bit like an OLE DB destination: it writes to something that looks like a table. During design time I want the component to update the list of available destination columns if they change - so I want Validate to return VS_NEEDSNEWMETADATA if it detects a change. However during runtime I only want to validate that the component will run ok. So I still want to check what the destination looks like but if, say, someone has just added a column then my determination is it is ok to procede with the execution.

ValidateExternalMetadata doesn't help in this case because I still want to validate against the destination. I just don't want to raise VS_NEEDSNEWMETADATA during runtime because it aborts execution and I can determine that although there is a change to the destination it is not one that will cause the component to fail.

Any thoughts and experience on this would be great to hear!

Martin

Hmmm...

I can accept that if the destination changes you still want to return VS_ISVALID based on some logic that you will code. But surely the same logic applies at design-time as well doesn't it?

I'm afraid I don't know of a way to determine if the Validate() method is running at design-time or execution-time or not.

-Jamie

|||

Hi Jamie,

Thanks a lot for your thoughts. Yes that's right: I definitely want to return VS_ISVALID at runtime. But I need to return VS_NEEDSNEWMETADATA at design time because (as far as I understand SSIS component coding) that is the only way to instruct the SSIS design environment that you have detected a change that should update the meta data. If I didn't return VS_NEEDSNEWMETADATA then the component's meta data would be frozen until the user did something goofy like change the destination table name property to something else and then back again - we've all used too many apps like that :-)

One thing I do want to avoid is a scheme where the designing user has to remember to flick some component property when they release their package into the runtime environment.

My current thoughts for a workaround are:

1. If I create a custom UI then I would know if the user pops up the custom designer - I could delay updating the meta data until then. Pretty cheesey though.

2. I introduce a package variable which could be set by the user and checked by my component. If it is true then Validate only ever returns VS_ISVALID. I think there is something about package variables only having their default value at design time, but can have other values at runtime.

Cheers,
Martin

|||

Hi Martin,

the curent Validate/ReinitializeMetadata mechanism is simply not designed for this. In our definition VS_NEEDSNEWMETADATA means there is a metadata mismatch and it will most likely cause the execution to fail, so it is not a valid state.

If you need this logic to be applied only at the design-time I would put it to a custom UI. You can add extra validation and refresh services to you implementation of IDTScomponentUI, and mimic the Validate/RMD protocol.

HTH,

Bob

Determining runtime or design time during Validate - workarounds?

I've seen a couple of posts in this forum on this subject. If anyone knows of a workaround it would be great to hear.

The problem is this. I'm writing a component that looks a bit like an OLE DB destination: it writes to something that looks like a table. During design time I want the component to update the list of available destination columns if they change - so I want Validate to return VS_NEEDSNEWMETADATA if it detects a change. However during runtime I only want to validate that the component will run ok. So I still want to check what the destination looks like but if, say, someone has just added a column then my determination is it is ok to procede with the execution.

ValidateExternalMetadata doesn't help in this case because I still want to validate against the destination. I just don't want to raise VS_NEEDSNEWMETADATA during runtime because it aborts execution and I can determine that although there is a change to the destination it is not one that will cause the component to fail.

Any thoughts and experience on this would be great to hear!

Martin

Hmmm...

I can accept that if the destination changes you still want to return VS_ISVALID based on some logic that you will code. But surely the same logic applies at design-time as well doesn't it?

I'm afraid I don't know of a way to determine if the Validate() method is running at design-time or execution-time or not.

-Jamie

|||

Hi Jamie,

Thanks a lot for your thoughts. Yes that's right: I definitely want to return VS_ISVALID at runtime. But I need to return VS_NEEDSNEWMETADATA at design time because (as far as I understand SSIS component coding) that is the only way to instruct the SSIS design environment that you have detected a change that should update the meta data. If I didn't return VS_NEEDSNEWMETADATA then the component's meta data would be frozen until the user did something goofy like change the destination table name property to something else and then back again - we've all used too many apps like that :-)

One thing I do want to avoid is a scheme where the designing user has to remember to flick some component property when they release their package into the runtime environment.

My current thoughts for a workaround are:

1. If I create a custom UI then I would know if the user pops up the custom designer - I could delay updating the meta data until then. Pretty cheesey though.

2. I introduce a package variable which could be set by the user and checked by my component. If it is true then Validate only ever returns VS_ISVALID. I think there is something about package variables only having their default value at design time, but can have other values at runtime.

Cheers,
Martin

|||

Hi Martin,

the curent Validate/ReinitializeMetadata mechanism is simply not designed for this. In our definition VS_NEEDSNEWMETADATA means there is a metadata mismatch and it will most likely cause the execution to fail, so it is not a valid state.

If you need this logic to be applied only at the design-time I would put it to a custom UI. You can add extra validation and refresh services to you implementation of IDTScomponentUI, and mimic the Validate/RMD protocol.

HTH,

Bob