One data file per CPU.. Why ?

They say tempdb requires one data file per CPU. Why ?
Also, why just tempdb ? Why not every user database to have the same
requirement of one data file per CPU?
Thanks
Who is "they say"? If youa re referring to the KBs that MS put out they
actually explain why. But in a nutshell it is because tempdb by nature
creates a lot of new tables and extents where as a user db normally only
creates new objects occasionally. There is contention on the portion of the
file that holds this information when you create lots of new objects
concurrently in SQL2000. Each file has its own allocation unit so you
effectively spread the load across each of the files. But I would read the
articles for more details.
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OEXgpr1NHHA.4376@.TK2MSFTNGP03.phx.gbl...
> They say tempdb requires one data file per CPU. Why ?
> Also, why just tempdb ? Why not every user database to have the same
> requirement of one data file per CPU?
> Thanks
>
|||Hi Hassan
To add to Andrews suggestion as well as the KB issue on this I suggest you
also read Kalen Delaney's "Inside Microsoft SQL Server 2005: The Storage
Engine" ISBN 0735621055
John

One data file per CPU.. Why ?

They say tempdb requires one data file per CPU. Why ?
Also, why just tempdb ? Why not every user database to have the same
requirement of one data file per CPU?
ThanksWho is "they say"? If youa re referring to the KBs that MS put out they
actually explain why. But in a nutshell it is because tempdb by nature
creates a lot of new tables and extents where as a user db normally only
creates new objects occasionally. There is contention on the portion of the
file that holds this information when you create lots of new objects
concurrently in SQL2000. Each file has its own allocation unit so you
effectively spread the load across each of the files. But I would read the
articles for more details.
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OEXgpr1NHHA.4376@.TK2MSFTNGP03.phx.gbl...
> They say tempdb requires one data file per CPU. Why ?
> Also, why just tempdb ? Why not every user database to have the same
> requirement of one data file per CPU?
> Thanks
>|||Hi Hassan
To add to Andrews suggestion as well as the KB issue on this I suggest you
also read Kalen Delaney's "Inside Microsoft SQL Server 2005: The Storage
Engine" ISBN 0735621055
John

One data file per CPU.. Why ?

They say tempdb requires one data file per CPU. Why ?
Also, why just tempdb ? Why not every user database to have the same
requirement of one data file per CPU?
ThanksWho is "they say"? If youa re referring to the KBs that MS put out they
actually explain why. But in a nutshell it is because tempdb by nature
creates a lot of new tables and extents where as a user db normally only
creates new objects occasionally. There is contention on the portion of the
file that holds this information when you create lots of new objects
concurrently in SQL2000. Each file has its own allocation unit so you
effectively spread the load across each of the files. But I would read the
articles for more details.
--
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OEXgpr1NHHA.4376@.TK2MSFTNGP03.phx.gbl...
> They say tempdb requires one data file per CPU. Why ?
> Also, why just tempdb ? Why not every user database to have the same
> requirement of one data file per CPU?
> Thanks
>|||Hi Hassan
To add to Andrews suggestion as well as the KB issue on this I suggest you
also read Kalen Delaney's "Inside Microsoft SQL Server 2005: The Storage
Engine" ISBN 0735621055
John

One CPU taking 85-90% utilization

Try asking them here. x-posted to:microsoft.public.sqlserver.server
[url]http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.server& cat=en_US_671e06d0-f20d-4bb3-9c6a-42c825ddb1dc&lang=en&cr=US[/url]
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Jitin Batra" wrote:
>I have a sql server which has correct affinity set but generating high cpu
> utilization on one of the CPU's. This is a HP Proliant DL 580 series
> server.
> kernrate tool shows ntoskrnl.exe with the highest hits. How can I find if
> there is a poor written driver which is generating these high interrupts
> like
> Nic card. I have enabled verifier.exe for nic driver and will reboot this
> weekend to see what it shows. if you have any other suggestion, then
> please
> let me know soon caus I cannot reboot this server during week days. I have
> also tried adplus to dump services.exe and sqlserve.exe processes but
> could
> not understand what it says. Please help.
That could be your NIC activity. Check the DPC rate on that processor.
DPCs are mostly associated with network driver activity.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Dave Patrick" <DSPatrick@.nospam.gmail.com> wrote in message
news:6BE0EF01-8FD5-4B22-876D-78B0DB2FDE2A@.microsoft.com...
> Try asking them here. x-posted to:microsoft.public.sqlserver.server
> [url]http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.server& cat=en_US_671e06d0-f20d-4bb3-9c6a-42c825ddb1dc&lang=en&cr=US[/url]
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Jitin Batra" wrote:
>
|||This is the perfmon stats for processor which shows DCP time is regularly
switching between P0 and P1 and there is not much diff in DPC rate. Interrupt
time and processor time is what shows quiet high for P1.
Processor
_Total01
% DPC Time0.6300.7270.532
% Interrupt Time35.0950.14370.047
% Privileged Time39.5167.47971.553
% Processor Time43.82015.07472.566
% User Time4.3047.5951.013
APC Bypasses/sec537.979238.574299.405
DPC Bypasses/sec0.0000.0000.000
DPC Rate13.0005.0008.000
DPCs Queued/sec614.149311.553302.596
Interrupts/sec157336.76677643.01279693.754
"Geoff N. Hiten" wrote:

> That could be your NIC activity. Check the DPC rate on that processor.
> DPCs are mostly associated with network driver activity.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Dave Patrick" <DSPatrick@.nospam.gmail.com> wrote in message
> news:6BE0EF01-8FD5-4B22-876D-78B0DB2FDE2A@.microsoft.com...
>

One CPU out of 32 pegged to 85%

Hey All,

We are running sql 2000 on 32 processor box. It is so weird that
around 4:30 PM every day one CPU spikes upto 85-90%. It continues to
go like that until rest of the day. We checked for jobs that will
start around that time but no luck. Only thing that we doubt now is a
job that starts around 2:30 pm. The job GZIPs the trace file from the
server and copies it to another server. Still, whats confusing is the
job starts around 2:30 PM but cpu spike starts around 4 PM. Can
someone tell me how can I find out what is running on that particular
processor.

Thanks for your help in advance.

SenthilYou might try using Performance Monitor. Select Process / % Processor
Time / All instances. This will help identify the EXE consuming the
processor.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Senthil" <senthil_tamil@.yahoo.com> wrote in message
news:46b4744a.0307111624.594a0159@.posting.google.c om...
> Hey All,
> We are running sql 2000 on 32 processor box. It is so weird that
> around 4:30 PM every day one CPU spikes upto 85-90%. It continues to
> go like that until rest of the day. We checked for jobs that will
> start around that time but no luck. Only thing that we doubt now is a
> job that starts around 2:30 pm. The job GZIPs the trace file from the
> server and copies it to another server. Still, whats confusing is the
> job starts around 2:30 PM but cpu spike starts around 4 PM. Can
> someone tell me how can I find out what is running on that particular
> processor.
> Thanks for your help in advance.
> Senthil

On a lighter note - What equipment do you use to develop?

This isn't a performance type of question, or whose CPU is bigger, but I'm more interested in knowing what people use out there as far as monitor setups go.

The root of the question stems from working in large package where there are many components laid out on the work flow. What do you use to work with large layouts? I constantly find it troubling to view enough of my package and am always zooming out to see more of the package. However, when doing that, the text becomes unreadable, of course.

I currently use a dual-monitor setup using my laptop and a LCD flat screen.

Resolutions on the laptop is 1920 x 1200 and the LCD monitor is 1280 x 1024. This helps to keep windows and output screens on one monitor while keeping the work flow on the other. Even still, I wish I had more screen real estate.

So, what say you?

I'm a consultant/contractor most of the time and I find that very few companies "get it".

I would be more productive with 3 monitors, but I usually only get 1 and usually only a small one at that. You might as well ask me to work with one hand tied behind my back or use only my index finger.

Ironic, considering companies pay more for consulting/contracting.

-- Benda

Currently using one 17" lcd monitor set to 1280 x 1024

|||How many objects do you have in your data flow at max ?

I have 40 in one of my packages and I use single monitor with the draggable scroller (I dont know what to call that) it is located at the junction of both the scrollbars. And it works for me. In the debug mode, I use to scroll as the control goes down.|||I have two monitors (1 @. 1400 x 1050, 1 @. 1280 x 1024). Most of our packages are less than 25 tasks, though the data flows get pretty large occasionally. Our packages that drive the overall process, on the other hand, can have 100+ Execute Package tasks in them.|||

Fahad349 wrote:

How many objects do you have in your data flow at max ?

As few as possible. Its rare that I have more than about ten. I prefer to go for many data-flows with few components rather than few data-flows with many components - thus I make heavy use of raw files.

To be honest though, rarely do you have the choice. Its the requirement that you are trying to fulfill that determines the complexity.

-Jamie

|||I have dual monitors @. 1600x1200, but I generally only use one of them for SSIS development. I agree with Jamie about data flow size and for performance/memory/restartability reasons keep my data flows as small as possible. For control flows I use collapsible sequence containers to group tasks and can hide those I'm not interested in. I can't say I ever wish for more real estate, and I never zoom.
|||I use my lappy 99% of the time, and the 17" screen with 1440x900 resolution is good but not great. I prefer to work at home on the 24" LCD with 1920x1200.

Scrolling isn't that much of a pain for me. I keep my pacakges flow to minimums. Next laptop will have better resolution screen.

Wes

On a lighter note - What equipment do you use to develop?

This isn't a performance type of question, or whose CPU is bigger, but I'm more interested in knowing what people use out there as far as monitor setups go.

The root of the question stems from working in large package where there are many components laid out on the work flow. What do you use to work with large layouts? I constantly find it troubling to view enough of my package and am always zooming out to see more of the package. However, when doing that, the text becomes unreadable, of course.

I currently use a dual-monitor setup using my laptop and a LCD flat screen.

Resolutions on the laptop is 1920 x 1200 and the LCD monitor is 1280 x 1024. This helps to keep windows and output screens on one monitor while keeping the work flow on the other. Even still, I wish I had more screen real estate.

So, what say you?

I'm a consultant/contractor most of the time and I find that very few companies "get it".

I would be more productive with 3 monitors, but I usually only get 1 and usually only a small one at that. You might as well ask me to work with one hand tied behind my back or use only my index finger.

Ironic, considering companies pay more for consulting/contracting.

-- Benda

Currently using one 17" lcd monitor set to 1280 x 1024

|||How many objects do you have in your data flow at max ?

I have 40 in one of my packages and I use single monitor with the draggable scroller (I dont know what to call that) it is located at the junction of both the scrollbars. And it works for me. In the debug mode, I use to scroll as the control goes down.|||I have two monitors (1 @. 1400 x 1050, 1 @. 1280 x 1024). Most of our packages are less than 25 tasks, though the data flows get pretty large occasionally. Our packages that drive the overall process, on the other hand, can have 100+ Execute Package tasks in them.|||

Fahad349 wrote:

How many objects do you have in your data flow at max ?

As few as possible. Its rare that I have more than about ten. I prefer to go for many data-flows with few components rather than few data-flows with many components - thus I make heavy use of raw files.

To be honest though, rarely do you have the choice. Its the requirement that you are trying to fulfill that determines the complexity.

-Jamie

|||I have dual monitors @. 1600x1200, but I generally only use one of them for SSIS development. I agree with Jamie about data flow size and for performance/memory/restartability reasons keep my data flows as small as possible. For control flows I use collapsible sequence containers to group tasks and can hide those I'm not interested in. I can't say I ever wish for more real estate, and I never zoom.|||I use my lappy 99% of the time, and the 17" screen with 1440x900 resolution is good but not great. I prefer to work at home on the 24" LCD with 1920x1200.

Scrolling isn't that much of a pain for me. I keep my pacakges flow to minimums. Next laptop will have better resolution screen.

Wes

OLTP and Reporting databases seperated?

we are using an object database for our OLTP but for reporting we havegot some issues about performance as the cpu becames a bottleneck.Andwe want to be able torun on low end computers...

One of our team members suggested to replicate the object database to aSQL table.But just a single one.The most denormalized thing ever.(358 coloumns)

is this the fastest way we can get in reporting?

*we don't want harddisk,ram or cpu to became a bottleneck. ( must run on cheap staff)

Replicating to SQL Server from object database is heterogeneous replication and replication is one of those things in SQL Server that is work in progress, so you may not get the results you expect. If you want to remain denormalized you can still reduce the columns by creating UNION ALL Views, if you choose to normalize the table start at the lowest level files and association because what you have now is just a flat file. Hope this helps.

|||thanks.I guess RDBMS is the fastest way to present table to UI.

oledb wait type

We have a production server that's cpu is throttled, and requests are timing
out. After running the get_waitstats sp, OLEDB is at the top of the list in
wait type (99%).
How do I determine which spid is involved in an oledb wait type?
This is a Windows 2000 sp 3 box, running sql 2000.
If you query master..sysprocesses, you can get the spids,
wait types and wait times.
If you want to see what the spids are actually executing,
pre-SP3, you can use:
dbcc inputbuffer(spid)
If you are on SP3, you can use:
declare @.Handle binary(20)
select @.Handle = sql_handle
from sysprocesses
where spid = <spid>
select * from ::fn_get_sql(@.Handle)
-Sue
On Mon, 7 Feb 2005 14:51:02 -0800, Ken
<Ken@.discussions.microsoft.com> wrote:

>We have a production server that's cpu is throttled, and requests are timing
>out. After running the get_waitstats sp, OLEDB is at the top of the list in
>wait type (99%).
>How do I determine which spid is involved in an oledb wait type?
>This is a Windows 2000 sp 3 box, running sql 2000.
>
|||OLEDB waittype is an overloaded waittype to begin with, it is set for:
* linked server calls
* bulk insert
* running SQL Trace
* running 6.x to 7.0 or 2000 conversion imports
* materializing virtual tables like select * from master.dbo.sysprocesses
and select * from master.dbo.syslockinfo
Also this wait state is set differently, since it is set when the call is
entered and unset when the call is done, so when you start a BULK INSERT it
will be set, when you are done it is unset, bt you are not really waiting,
you are doing real work, only inside an OLE DB provider, for example BULK
INSERT is hosted in the IMPROV.DLL provider, SQL Trace and materializing
virtual tables are internal providers.
In other words, high OLEDB waitstats are not very often a problem.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4utf015ilc6kaha46e8c5pme6972q7gdo7@.4ax.com...
> If you query master..sysprocesses, you can get the spids,
> wait types and wait times.
> If you want to see what the spids are actually executing,
> pre-SP3, you can use:
> dbcc inputbuffer(spid)
> If you are on SP3, you can use:
> declare @.Handle binary(20)
> select @.Handle = sql_handle
> from sysprocesses
> where spid = <spid>
> select * from ::fn_get_sql(@.Handle)
> -Sue
> On Mon, 7 Feb 2005 14:51:02 -0800, Ken
> <Ken@.discussions.microsoft.com> wrote:
>

oledb wait type

We have a production server that's cpu is throttled, and requests are timing
out. After running the get_waitstats sp, OLEDB is at the top of the list in
wait type (99%).
How do I determine which spid is involved in an oledb wait type?
This is a Windows 2000 sp 3 box, running sql 2000.If you query master..sysprocesses, you can get the spids,
wait types and wait times.
If you want to see what the spids are actually executing,
pre-SP3, you can use:
dbcc inputbuffer(spid)
If you are on SP3, you can use:
declare @.Handle binary(20)
select @.Handle = sql_handle
from sysprocesses
where spid = <spid>
select * from ::fn_get_sql(@.Handle)
-Sue
On Mon, 7 Feb 2005 14:51:02 -0800, Ken
<Ken@.discussions.microsoft.com> wrote:

>We have a production server that's cpu is throttled, and requests are timin
g
>out. After running the get_waitstats sp, OLEDB is at the top of the list in
>wait type (99%).
>How do I determine which spid is involved in an oledb wait type?
>This is a Windows 2000 sp 3 box, running sql 2000.
>|||OLEDB waittype is an overloaded waittype to begin with, it is set for:
* linked server calls
* bulk insert
* running SQL Trace
* running 6.x to 7.0 or 2000 conversion imports
* materializing virtual tables like select * from master.dbo.sysprocesses
and select * from master.dbo.syslockinfo
Also this wait state is set differently, since it is set when the call is
entered and unset when the call is done, so when you start a BULK INSERT it
will be set, when you are done it is unset, bt you are not really waiting,
you are doing real work, only inside an OLE DB provider, for example BULK
INSERT is hosted in the IMPROV.DLL provider, SQL Trace and materializing
virtual tables are internal providers.
In other words, high OLEDB waitstats are not very often a problem.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4utf015ilc6kaha46e8c5pme6972q7gdo7@.
4ax.com...
> If you query master..sysprocesses, you can get the spids,
> wait types and wait times.
> If you want to see what the spids are actually executing,
> pre-SP3, you can use:
> dbcc inputbuffer(spid)
> If you are on SP3, you can use:
> declare @.Handle binary(20)
> select @.Handle = sql_handle
> from sysprocesses
> where spid = <spid>
> select * from ::fn_get_sql(@.Handle)
> -Sue
> On Mon, 7 Feb 2005 14:51:02 -0800, Ken
> <Ken@.discussions.microsoft.com> wrote:
>
>