Question About Threads And SQL Licensing
Jun 16, 2006
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
View 1 Replies
ADVERTISEMENT
Feb 29, 2008
How can i delete my old threads from Mythreads.
Any Body
View 3 Replies
View Related
Jan 24, 2001
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
View 4 Replies
View Related
Jul 21, 2004
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?
View 3 Replies
View Related
May 31, 2007
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?
View 9 Replies
View Related
Jul 20, 2005
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 Related
Nov 20, 1998
that 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.
View 6 Replies
View Related
Aug 30, 2005
Hi,I have an application where I need to find out about the followinginformation regarding SQL server:Processors enabledi.Threads allocatedii.PriorityCan somebody throw some light on this. How are the processors relatedto the threads running and the priority is w.r.t. what?Thanks,Verve.
View 1 Replies
View Related
Oct 10, 2007
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...
View 4 Replies
View Related
Jan 15, 2008
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?
View 7 Replies
View Related
Mar 12, 2007
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
View 2 Replies
View Related
Aug 2, 2007
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 Related
Jul 20, 2005
using 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
Aug 22, 2007
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!
View 3 Replies
View Related
Aug 9, 2006
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.
View 9 Replies
View Related
Jun 21, 2007
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
View 15 Replies
View Related
Apr 3, 2006
That is a SqlException I got at a...
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
Anyone an idea what THAT means? How do I cause it? How can I work around it?
View 4 Replies
View Related
Dec 21, 2004
Our company is in the retail business, thus, the window for processing cubes is very small during Christmas season (only 4 hours each day).
To speed things up, we have partitioned our cube at monthly level so, potentially, 12 threads can be run simultantsly. However, when I looked at DTS, I am not so sure whether or how it can accomplish that task. Has anyone tried this before or is aware of another third party tool can do the trick?
thx in adcance,
Carl.
View 2 Replies
View Related
Mar 3, 2008
Hi All
I have been asked by developers if there is any advantage in processing multiple clustering models simultaneously by using AMO and multiple threads as against processing one after another.
I have limited experience with Analysis Services but based on my reading I don't see this method providing any advantage.
Does anyone have any recommendations or advice? The system Enterprise Edition running on an x86 Server with 2 dual core processors and 4GB of RAM. Would the answer alter if the server running x64 version of SQL Server and Windows.
Thanks
Nadreck
View 3 Replies
View Related
May 12, 2008
Are there any draw backs to spawning additional threads inside of the script tasks for SISS packages?
View 4 Replies
View Related
Jan 24, 2006
I found a peculiar thing today while working with SQL Mobile in a multithreaded application (VS2005, application for Pocket PC 2003).
I created a class which has one SqlCeConnection object. Every time I call a function to insert/select/delete something from the local db, I open the connection, execute the query an then close the connection again.
But when I'm calling a function from the db class in thread 1 and in the meantime call a different function (from the same db class of course) in thread 2, things go wrong. Because when function 1 wants to close the connection, function 2 is still using the connection and it will crash my application with a native exception (0xC0000005: access violation).
I can see why the error is happening, but shouldn't there be a nice .NET handled exception instead of a native exception which grinds my app to a hold?
(A workaround I use now is to use multiple connection objects instead of one, but I thought I'd give this feedback anyway)
View 5 Replies
View Related
Nov 29, 2006
Recently I'm working on a multi-thread solution based on SQL-Server, now I'm facing such a problem:
Suppose I have process No.1(with multi-threads) inserting data to Table A, which has its identity column auto generated. And process No.2(also with multi-threads) retrieving data from Table A ,generate some records and insert the result into Table B. Both of these two processes are doing batch processing(batch retrieving and batch writing), and they are running parallelly.
Now since process No.2 retrieve data sequencely by the identity of Table A, it found there exists missing results. This is due to that records with bigger identities are not necessarily commited earlier than those who have smaller identities.
One direct solution is add one flag field in Table A indicating whether this record has been processed by process No.2, and each time it was processed , the field will be set. But unfortunatelly the table structure is not supposed to be modified.
So is there any other good solutions for this problem? Thanks.
View 7 Replies
View Related
Jul 16, 2001
Hi Guys,
I am searching for some information on achieving performance improvement by spawning multiple threads in single Stored procedure or rather say within Single Database connection. We have a batch process that updates around 200 tables and each table update takes around 2 mnts. I am trying to optimize this by running these updates in parallel rather than sequential. These all tables are mutually exclusive. I have written a stored procedure which updates these tables in loop. Concern is that every update statement waits for other to get over. I am calling this Sp from Java application. One crude way will be opening multiple connections to database each running separate T-SQL statement. It comes with lot of overhead in opening connections .Is there any way I can force explicitly in T-SQL stored procedure to spawn a new thread for every Update statement. In case I try to do same process from a Java connection.. is there a way I can open multiple threads for each statement under same database connection.
Thanks,
Vikalp
View 1 Replies
View Related
Jul 24, 2012
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 Related
Aug 14, 2007
Hi,
I am using distributed transactions where in I start a TransactionScope in BLL and receive data from service broker queue in DAL, perform various actions in BLL and DAL and if everything is ok call TransactionScope.Commit().
I have a problem where in if i run multiple instances of the same app ( each app creates one thread ), the threads pop out the same message and I get a deadlock upon commit.
My dequeue SP is as follows:
CREATE PROC [dbo].[queue_dequeue]
@entryId int OUTPUT
AS
BEGIN
DECLARE @conversationHandle UNIQUEIDENTIFIER;
DECLARE @messageTypeName SYSNAME;
DECLARE @conversationGroupId UNIQUEIDENTIFIER;
GET CONVERSATION GROUP @conversationGroupId FROM ProcessingQueue;
if (@conversationGroupId is not null)
BEGIN
RECEIVE TOP(1) @entryId = CONVERT(INT, [message_body]), @conversationHandle = [conversation_handle], @messageTypeName = [message_type_name] FROM ProcessingQueue WHERE conversation_group_id=@conversationGroupId
END
if @messageTypeName in
(
'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog',
'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
)
begin
end conversation @conversationHandle;
end
END
Can anyone explain to me why the threads are able to pop the same message ? I thought service broker made sure this cannot happen?
View 11 Replies
View Related
Mar 7, 2008
Hi,
I am getting deadlock on activated procedure which I am using to receive message from the Service Broker Queue.
Deadlock details:
Two threads are tring to do delete on internal table queue_messages_122847900 ends up in a dead lock.
Activated procedure code
RECEIVE TOP(1) @xmlMessage = message_body,
@handle = conversation_handle,
@message_type = message_type_name
FROM TransactionQueue;
IF (@message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
END CONVERSATION @handle;
RETURN 0
END
.........................
.........................
After this I do process the message and some other processing
And then
END CONVERSATION @handle;
Note I do have single conversation group
Is their a problem in the way I am receiving and processing messages. Is it possible because of the delay between RECEIVE and END CONVERSATION same message is read by two different threads.
Thanks
View 1 Replies
View Related
Jul 6, 2015
I have been tasked with processing a large tabular cube using SQL AS 2014 (with latest CUs).The three Fact tables having 1.2 billion rows (in each table) have been divided into 30 vertical partitions to aid in parallel processing. So around 40 million rows per partition.
Using SQL Profiler to monitor the Row counts (IntegerData) of records processed seems to max out around 2 million rows per minute, then tapers down to about 200k /minute.
The processing is taking over 14 hours and I need to get it lower if possible. The server has 48 cores (2.66MHz) and over 1TB RAM installed. But I really don't ever see CPU exceed 20% having a maximum of 206 threads running on the instance msmdvr.exe
Available RAM is always at least 30% (or 300GB).
I have increased the Vertipaq MIN/MAX 60%/80%
I have increased the OLAP / Processing / Max Thread Pool Min 500 and Max to 1000.
The connection properties have been increased to allow 100 connections, the majority of the processing consumes about 92 connections for the 90 large partition views for the facts.
What can be done to increased the server resource utilization and decrease processing times?
I have increased both
View 5 Replies
View Related
Jun 18, 2014
I 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 .
View 3 Replies
View Related
Feb 18, 2015
SQL 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.
View 3 Replies
View Related
Jan 30, 2007
Is there a way to get Licensing information(product key) off an installed version of SQL Server 2005? Most Microsoft Software has the product key in the about dialog box, but SQL Server doesn't have this anywhere.
Thanks,
View 1 Replies
View Related
Nov 2, 2000
Help me please!!!
I'm going crazy trying to find out the license deal for SQL Server 2000 Developer Edition. It seems that here (in the UK) the Developer edition exists and you can buy it but, Micrisoft (UK) and various resellers etc don't know the licensing deal!!!
Now, i've read a snippet somewhere about the developer edition that for a fixed price of around $499 you get a server license limited to 8 connections and you don't have to buy any CALs! However my reseller thinks that you still have to buy CALs up to maximum of 8? Well, i don't think so!!
Has anyone got this edition and know the deal?
Many thanks...
View 1 Replies
View Related
Feb 2, 2001
We are about to deploy an application that uses SQL Server 7.0. Enterprise edition running on a dual processor machine. We expect a maximum concurrent usage of about 500 users, normally about 150/200. Could anyone advise me what the cheapest license arrangement would be, I've had conflicting views from Microsoft and two third party software suppliers? We intend to use the machine in future for other applications but as yet have very little idea of usage.
Joe
View 1 Replies
View Related
May 26, 2008
Can any one please clear my confusion. when we install 2000 it always give option ofr licence (processor or per seat) but i installed 2005 couple of times but can' see any option in whole installation about type of licenscing. any one have any idea where and how to choose type of licence for SQL Server 2005.
Thanks
View 1 Replies
View Related
