Hi,
I have an application where I need to find out about the following
information regarding SQL server:
Processors enabled
i.Threads allocated
ii.Priority
Can somebody throw some light on this. How are the processors related
to the threads running and the priority is w.r.t. what?
Thanks,
Verve.
Is there a limit to the number of processors that can interact with SQL Server 7?
Also, what is the minimum amount of memory necessary to run SQL Server 7?
Can I install SQL Server on a machine and use less than the # of processor on the machine. In a UNIX world, I'd call it LPARing with Oracle and AIX, and they only let me do this with Enterprise Edition. With Windows, I think the only way is using virtual machines and attaching processors to them? Do any vendors offering LPARing? Can I take any edition of SQL Server and subcapacity price so that I only pay for the processors I'm using?
What about SS Express? It only scheds to a single core - so could I put that on a larger machine?
Thanks!
Hi,
I'm trying to troubleshoot a SQL problem that we are having and I'm having difficulty with identifying the guilty process.
Using NT performance monitor I am monitoring all active Threads on the system and I have noticed that one particular SQLSERVR thread (then number obviously changes with each server restart) is hogging 100% CPU.
Is it possible to find out what process a particular thread number relates to ?
As far as I can tell the SQL SPID (from Enterprise manager) does not correlate to a SQL Thread.
Thanks,
Tim
Hello all,
We've had a problem for a few months now that has completely stumped us. We are running a heavily cursored massive data manipulation process on a 32 bit SQL Server instance running on a virtual machine, running ontop of VMWare, with the following specs
Processors: 2x2674MHz processors
Memory: 4GB
RAID 10 disk config
When we run our process on this machine, in total it runs in 30 hours.
When this process is run on another 32 bit server with the following specs
Processors: 8x3658MHx processors
Memory: 8 GB
SAN w/ RAID 5 disk config
It runs 25% slower
But here is the real kicker. When this process is run on a 64 bit server with the following specs
Processors: 8x3658MHz processors
Memory: 8 GB
SAN w/ RAID 5 disk config
It runs 75% slower.
This process consists solely of stored procedures written in TSQL. The weird thing is that on our smaller server, the CPUs' % utilization are evenly balanced (at 20-30%) when this large data manipulation process is running. However on the bigger servers, SQL Server latches onto a single processor and doesn't load balance across other processors. Such that what we're seeing is that only one processor out of the eight will be utilized and it will be throttled at 90% while the other 7 are at zero.
The default configuration settings in all three places.
Has anyone ever seen any behavior like this, where only one processor gets used by SQL Server during processing? Granted our processes are single threaded b/c they are using cursors but, it seems that the single thread shouldn't be restricted to one processor.
Any thoughts?
we are queirying an stored procedure multiple times same time,from our application. In this case, few processes executing successfully and few getting failed with error "50000 error executing the stored procedure" and if we run thesame process again its getting executed sucessfully.Does the MySQL cannot handle multiple threads same time?
View 9 Replies View RelatedI have two tables called ECASE and PROJECT
In the ECASE table there is trigger to get the max value of case_id column in ecase based on project and increment one to that case_id value and insert into ecase table .
When we insert a new record to the ECASE table this trigger calls and insert the case_id column value.
When i run with multiple threads , the transaction is rolled back because of trigger . The reason is , on the project table the lock is happening while getting the max value of case_id column based on project.
I need to prevent the deadlock .
Hi all,
I am using sql server 7 i am having 4 processors in development server.
How i have to allocate those servers, means all 4 do i need to allocate to sql only or 3 processors.
same like 8 processors in production, how many processors to sql..
pls tell me the way..and how..
thank u all
esh
Specs: Dell 6350 Poweredge, 4 Xeon 500, 4GB Ram
SQL 7 Sp1.
At some point, my 4 processors began a cycle of Peaks and Valleys.
AFter stopping ALL processes using the SQL server the processors were still doing this Max, then none, Max, then none.
I think the oddest part of this is that all 4 processoers were Exactly the same Peaks/Valleys from my Workstation (perfmon).
Anone had their processors do this? HELP PLEASE.. .
While installing SQL2k I selected a 4 processor liscense aggreement. The server only has two. Does anybody know of a technial reason I should change it back? Can it be done without reinstalling?
Sidney Ives
Database Administrator
Sentara Healthcare
We are looking at upgrading our SQL Server machine to multiple processors.
Will this act alone result in a performance improvment?
how to find out how many processors are licensed on the server?
=============================
http://www.sqlserverstudy.com
We have a 200MHz Pentium Pro based machine, with 128MB RAM running SQL Server 6.5. Because of performance issues, we are contemplating an upgrade to a dual 200MHz Pentium Pro processor with 256MB RAM. However, the vendor we are dealing with has suggested an upgrade to a single Pentiun II/333MHz first, and if this still causes problems, then to a dual P II/333MHz.
Does anyone have any suggestions from similar upgrades that they may have undergone?
We have 72MB allocated to SQL Server.
I'm spec up a new server At the minimum I'll specify a quad core cpu. What would be the best way to utilize the cores? Assign a core to specific processes (tempdb?) or let sql server figure out the best way to used the processors it finds?
View 1 Replies View RelatedSQL Server 2008 r2 - 6 GB memory...I attempted a backup on a 500GB database but it was taking way too long. I checked the resources on the box and saw the CPU at 100%. I checked the SQL Server activity log and saw a hung query (user was not even logged on) that had multiple threads so I killed it and now the CPU utilization is back to normal.
Trouble is, now all of the threads in the activity monitor for the backup show 'suspended' and the backup appears to be not doing anything.
Is it possible in SQL Server 2005 to limit the number of processors used? For cost reasons, we are consolidating servers and want to start running SQL Server 2005 on one of our dual-processor Win2K3 machines instead of the standalone machine it's currently running on. Because we have about 75 users, it's only cost effective to purchase a processor license (vs. a server license with CALs). But right now we only need and can only afford a single processor license, not two. So...
Is there any way in 2005 to limit the number of processors used so that we only need to purchase one processor license? I know in 2000 you could set this on the "Processor" tab of the "SQL Server Properties" dialog. In 2005, is this accomplished by unchecking the "Processor Affinity" and "I/O Affinity" checkboxes for processor #2 on the "Processors" page of the "Server Properties" dialog? If I uncheck these two options does that fully disable SQL Server 2005 from accessing the second processor in any way? From things I've read I can't tell if it restricts access to the second processor completely or if it just places some limitations on the ways it accesses the second processor.
Also, the licensing information for SQL Server 2005 leads me to believe that if you are going down the "processor licensing" route that you have to buy a processor license for every processor that the OS itself has access to and not just what processors SQL Server has access to. I thought I understood that in SQL Server 2000 the licensing information did allow you to buy a processor license just for each processor that SQL Server 2000 had access to, but has that changed for 2005?
Hope someone can provide some clarification on limiting processor access and the licensing implications for SQL Server 2005.
Thanks,
Scott
Hello,
Yesterday I was looking to the processor usage in the Task Manager of Windows NT when a script of mine was running. The script was an InfoPump Script; which is a tool from the DecisionBase suite from CA (was previously owned by Platinum). This script contains SQL statements that select data from several tables and stores the result into another table. The SQL code used for this looks fine to me.
The query was running on a Compaq Proliant 5500 with 4 500 Mhz Xeon processors, 1 GB RAM, NT Server 4, SP 5, RAID 5. The SQL Server is configured to use all resources and SQL has normal priority on NT.
When the select part was running al four processors were used for about 75% and when the store happens only 1 processor is used for 100%.
Why is the store not spread over all four processors? It only uses one processor and it seems to be a bottleneck.
Stef
Hi,Is there a reason why we have to pay more for licensing for a differentkind of processor?Why are we not charged for the Hyperthreading on some processors also.If Oracle is really conserned about the low end business market (smalland medium), then they should drop their attitude on Dual Coreprocessors.If they start charging as if it was a normal processor, and ask thenormal price, then they would get more of this market coming in.As long as Oracle keeps on having the attitude of charging more,because Intel or some other cpu vendor decided to mprove theirprocessors because of overheating problems, I will have the attitudethat I will keep on reoccomending alternatives for Orcle like Mysql /Postgre sql / Sybase, etc to the small/medium sector.Microsoft's pricing model on double core processors suddenly soundallot better.Oracle are shooting themselves in the foot! Or am I the only personfeeling this way?Shaun O'Reilly
View 2 Replies View RelatedHow can i delete my old threads from Mythreads.
Any Body
When Standard Edition says it supports 4 processors, is this just the physical processor or do we have to factor in multiple cores?
If SE supports 4 physical quad-core processors, is it written to optimally utilize the quad-core technology or would I be better off using Enterprise Edition?
Dave
I have an app that is critical to our business. It handles and syncronises several SQL Servers, checks integrety etc. I need to make the app so it can run a few things at once. Does anyone have any experience with this? Currently we use Delphi and ADO. I have been fiddling with DMO to get more performance - I am not sure ADO is very quick for some I tasks I need to do.
I suppose my main question *really* is does ADO/DMO multi-thread and has anyone tried it. If not how do people do it?
Here's what I know about max worker threads:
http://technet.microsoft.com/en-us/library/ms187024.aspx
http://sqlblogcasts.com/blogs/thepremiers/archive/2007/05/17/max-worker-threads-configuration-in-sql-server-2005.aspx
and this is kind-of related:
http://arstechnica.com/news.ars/post/20070529-microsoft-exec-next-version-of-windows-to-be-fundamentally-redesigned.html
How do you know how many threads are being used and how many of those are being shared? Or whether they are all even being used? Are there PerfMon stats for this?
Everyone,I have a data warehouse that at the moment includes around 2500hundred jobs. I am planning for a worst case scenario and would liketo increase the maximum number of sql server threads so that more jobscan execute simultaneously. Could this pose a problem and if so, atwhat number of maximum threads??Thanks!
View 1 Replies View Relatedthat if you reduce your max worker threads it will enhance the ability to kill processes (spid###) which cannot be killed.
Recently, we have been experiencing runaway processes. If you run these processes continuously, eventually it will run without failure. We are investigating as to the reasons why this is happening. At this very moment I am sifting through the code to try and find an answer. Evidently, the process hangs and cannot be killed. We are then forced to down the server and I wish not to do this so often. If I reduce the max worker threads from it's current number now (512) to a lesser number. Will this help? and if so, by how much should this number be decreased.
Thanks a gig for your help.
We've invested ourselves heavily in subscription-based reporting where the SSRS service is responsible for rendering and delivering reports (to email, file shares, printers, document repositories, etc). We figured that this would be a model that would allow for easier scaling. Users submit their reports and allow SSRS to deliver them in due time. The biggest part of our reporting is now done via subscriptions.
However, adding long-running reports and short-running (but very critical) reports together to the same SSRS database has proven problematic. The long-running reports eventually make make the short reports starve for CPU time.
Does anyone know if there is a way to implement a CPU resource allocation strategy so that short running (but critical) reports will always have the ability to run? For example, it would be nice if certain user accounts or even report paths (RDL's) could have dedicated CPU resources (eg SSRS threads on which to run).
In other words, without creating additional ReportServer databases, I'd like a "pool" of threads for "severity 1" report subscriptions, a separate "pool" of threads for "severity 2" and so on. Then we'd be able to make sure that our critical subscriptions would get a chance to run. Sounds pretty straight-forward, right? Can't figure out how to go about this...
Sometimes ( on average once every two weeks ) I am getting the following error message:
Code BlockError: 2008-01-15 06:51:02.91
Code: 0xC0047024
Source: DF FACT SC 1 DTS.Pipeline
Description: The number of threads required for this pipeline is 98, which is more than the system limit of 64. The pipeline requires too many threads as configured. There are either too many asynchronous outputs, or EngineThreads property is set too high. Split the pipeline into multiple packages, or reduce the value of the EngineThreads property.
End Error
This is one of the final stages of the ETL, so several other packages are finishing correctly before this package is run.
When we restart the complete ETL all other packages are automaticly skipped, and when the ETL arrives to this package it runs without any problems.
So my questions are:
What does this error message mean?
Is this "64" a SSIS setting or a SQL Server setting or a server setting?
Can we increase this setting?
The number of engine threads is set to 5 ( default ), what is the relation between the engine thread setting and the system limit of 64?
Can we safely reduce the engine threads property?
What is causing the SSIS package to need 98 engine threads?
I am working on a project that will require the use of SQL Server 2005 Workgroup Edition. We were planning to use the version that comes with 5 CALs instead of the version licensed based on processors due to the enormous cost difference. Our customer who will be using this is a goverment agency and ms charges them ~$800 for the 5 user version, but the version licensed by processor is ~$20000. This software will be potentially installed in lots of locations so cost is a big factor.
The application I am working on is using c#. I am being told that if I use threads, each thread will require a CAL. Is that correct? I can not find anything in the licensing information that explicitly states this. It was my understanding that a CAL based on user or device could make as many connections to the db as needed.
Thanks
SQL Server 2005 gives you a possibility to create managed triggers. In a managed trigger I can create a new thread and process trigger event in various ways. My question is that are there any reasons why I should NOT start new threads in database triggers? The following code shows how I could create new threads. Do you see that this could cause any errors or problems in SQL Server functionality? My goal is to minimize the trigger effects in a database overall performance.
'This handles database updates of AdventureWorks Person.Contact table.
<Microsoft.SqlServer.Server.SqlTrigger(Name:="UpdateContact_Trigger", Target:="Person.Contact", Event:="FOR UPDATE")> _
Public Shared Sub UpdateContact_Trigger()
'Notify:
SqlContext.Pipe.Send("Trigger FIRED")
Dim triggContext As SqlTriggerContext = SqlContext.TriggerContext()
'Ensure that it was update:
If triggContext.TriggerAction = TriggerAction.Update Then
'Open a connection:
Using conn As New SqlConnection("context connection=true")
conn.Open()
'Fetch some information about the update:
Using sqlComm As New SqlCommand
sqlComm.Connection = conn
sqlComm.CommandText = "SELECT ContactID, FirstName, LastName from INSERTED"
Using rdr As SqlDataReader = sqlComm.ExecuteReader
If rdr.Read Then
'Process the data in a separate thread. The thread could send a message to MSMQ about the change
'or process if differently. The purpose of using separate thread is avoid performance hit
'in the application / database operation.
Dim oData As New TriggerData("update", rdr.GetInt32(0).ToString, "contact")
Dim trd As New Threading.Thread(New Threading.ThreadStart(AddressOf oData.ProcessEvent))
trd.IsBackground = True
trd.Start()
End If
End Using
End Using
End Using
End If
End Sub
I want to select all threads even though the count(Messages.TitleID) is zero.. Is like when u start a new thread with no messages currently posted. How can i modify the codes to do it?SELECT Thread.ThreadTitle, COUNT(Messages.TitleID) AS NoOfMessage, Thread.ThreadID FROM (Thread INNER JOIN Messages ON Thread.ThreadID = Messages.ThreadID) GROUP BY Thread.ThreadTitle, Thread.ThreadID
View 2 Replies View Relatedusing osql to apply SPs in mutiple threadsHello,I got a weird problem when I was using osql to apply scripts for msdedatabase in multiple threads mode. Sometime 2 sps were missing duringthe whole apply process, sometime not, and seems like only those twoSPs met the problem. No error was appeared. Did anyone meet sameproblem before? Or any possible solutions?Thank you very much!
View 4 Replies View Related
hi,
do i need to use specially synchronized code if i have multiple threads inserting, updating and reading rows to and from the same database?? in this case, i know that no 2 threads will try to insert or update the exact same row into the DB, however, multiple threads might try to read the same row from the database.
thanks!
Hi, I'm trying to stress test my web application, but when I get high load, the queries that used to take 10-20 ms starts taking 500 - 2000+ ms. Or to put it another way, when i run them single threaded i can do about 43000 a minute, when they are run in paralell it drops to about 2500 a minute.
What can i do about this ?
There are severeal queries thats affected, but here is one example:
update [user] with (ROWLOCK XLOCK) set timestamp = getdate() where userid = 1''
btw: im running sql server 2005 sp 1. The stress test is run on 3 machines total (web, sql and client) the client is simulation 400 users, cliking a page as soon as the last one is loaded, ie there will always be 400 page requests.
am experiencing excessive SSB thread block'n...sql error log is reporting LOTS of Resource Monitor messages about non-yielding threads (nothing meaningful can be surmised from it).
I am running on a 4way 64bit 2003 box w/6gb ram!!!
SSB architecture is simple implementation... Leveraging async trigger(s) in 42 db's (all on same instance) that post (via srvc) into a mstr db queue...where a listener is pull'n them off and applyies to a table (trying to avoid excessive 1205's that I was experiencing using sync trigger approach before)....messages sit in respective db's trans queue and draining of queues is extremely SLOW!!!! I mean SLOW!!!
Eventually SqlServer.exe process pegs out ALL processors!!! Only can reboot box to get connectivity back...~
Anyone have this experience!? (really hope not...but I need help)
Have completely cycled SSB machinery (via disable/enable)...and have even stepped thru enabling one db at a time...but still very poor performance!!!
Anyone?
-mt
sp_who output here...
BACKGROUND sa . 16 NULL RESOURCE MONITOR
BACKGROUND sa . . NULL LAZY WRITER
SUSPENDED sa . . NULL LOG WRITER
BACKGROUND sa . . master SIGNAL HANDLER
BACKGROUND sa . . NULL LOCK MONITOR
sleeping sa . . master TASK MANAGER
BACKGROUND sa . . master TRACE QUEUE TASK
sleeping sa . . NULL UNKNOWN TOKEN
BACKGROUND sa . . master BRKR TASK
BACKGROUND sa . . master TASK MANAGER
SUSPENDED sa . . master CHECKPOINT
sleeping sa . . master TASK MANAGER
sleeping sa . . master TASK MANAGER
BACKGROUND sa . 16 ThompsonTractorD43 KILLED/ROLLBACK
sleeping sa . . master TASK MANAGER
BACKGROUND sa . . master KILLED/ROLLBACK
BACKGROUND sa . 16 master KILLED/ROLLBACK
sleeping sa . . master TASK MANAGER
BACKGROUND sa . . master BRKR TASK
BACKGROUND sa . 16 master BRKR TASK
sleeping sa . . master TASK MANAGER
sleeping sa . . master TASK MANAGER
sleeping sa . . master TASK MANAGER
sleeping sa . . master TASK MANAGER
BACKGROUND sa . 16 YancyMachineryCat KILLED/ROLLBACK
BACKGROUND sa . . master BRKR EVENT HNDLR
BACKGROUND sa . . master BRKR TASK
sleeping NT AUTHORITYSYSTEM REFINERY1 . msdb AWAITING COMMAND
sleeping NT AUTHORITYSYSTEM REFINERY1 . msdb AWAITING COMMAND
sleeping NT AUTHORITYSYSTEM REFINERY1 . msdb AWAITING COMMAND
sleeping NT AUTHORITYSYSTEM REFINERY1 . msdb AWAITING COMMAND
SUSPENDED NT AUTHORITYSYSTEM REFINERY1 . msdb DELETE
sleeping fastironweb DETROIT . Cat_Lvl3 AWAITING COMMAND
sleeping mike REFINERY1 . master AWAITING COMMAND
SUSPENDED NT AUTHORITYSYSTEM REFINERY1 . distribution WAITFOR
sleeping mike REFINERY1 . Cat_Cfsc AWAITING COMMAND
sleeping mike REFINERY1 . Cat_Cfsc AWAITING COMMAND
sleeping mike REFINERY1 . Cat_Cfsc AWAITING COMMAND
RUNNABLE mike REFINERY1 . Cat_Cfsc SELECT INTO
sleeping NT AUTHORITYSYSTEM REFINERY1 . msdb AWAITING COMMAND