Resources Usage
Oct 20, 2005Hello, I need to know how to see the cpu and memory usage per session, and the cause (What query is causing It)
View 2 Replies
ADVERTISEMENT
SQL Server 2008 :: Restrict Resources Usage Based On Individual Databases In Resource Governor?
Aug 12, 2009How to restrict resources usage based on individual Databases in resource governor?
We have many databases in one instance; I would like to restrict resource usage to each database respectively.
I created 2 pools as pool_login, pool_DBNAME, and 2 workload groups as GroupLogin,GroupDBNAME, and also the classifier function.After setup above, I use following statement to check what sessions are in each group .
Even if there are spids which are accessing database DBNAME, I can’t see that they fall into the group GroupDBNAME and pool pool_DBNAME.
SELECT s.group_id, CAST(g.name as nvarchar(20)), s.session_id, s.login_time, CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS nvarchar(20))
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_resource_governor_workload_groups g
ON g.group_id = s.group_id
ORDER BY g.name
GO
Following is the code to create pool, group,classifier function:
USE master
GO;
-- Create a resource pool pool_login.
CREATE RESOURCE POOL pool_login
WITH
[Code] ....
-- Create a workload group to use this pool.
CREATE WORKLOAD GROUP GroupLogin
USING pool_login;
GO
CREATE WORKLOAD GROUP GroupDBNAME
USING pool_DBNAME;
[code]....
-- Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO
Computing The CPU Usage ,memory Usage For An Inserted Record
Nov 2, 2007
I have a client program that writes to sql server database 10 records per second . i want to compute the CPU usage and the memory usage for the whole program or CPU usage,memory usage for the insert statement in the program .
Can anybody help me with this?
CPU Usage(%), Logical IO Performed (%) Usage For Adhoc Queries Is 90%
Sep 7, 2007
Hello, When I am seeing SQL Server 2005 Management studio Server Dashboard> I am seeing my(USERS) databases and msdb database usage is very small % of in CPU Usage(%), Logical IO Performed (%) Usage pie chart.
90% of Total cpu usage is showing for Adhoc Queries. what excatly this means in Dashboard? if application uses more than it would have shown in Database level or not?
sicerely this dashboard is good, if any one is watching daily, please advice their experiences here.
Thanks in advance. Hail SQL Server!
SQL Server 2012 :: Query To Get CPU Usage / Memory Usage Details Of Server?
Jan 30, 2014providing a query for fetching the data for CPU Usage, Memory usage, blocking and all details ...
I want to create a job which will run on a Node every 15 min and store data in a table for each instance...
DMV is not giving more stuff and xtended events not sure if i can store that data into a table?
Why Does ADO.NET Used So Many Resources?
Nov 10, 2003I used SqlConnection/SqlDataReader to read data from SQL Server 2000 in my ASP.NET project, but I found that the CPU usage is too high, and I used SQL Query Analyzer to run the same SQL statement, The CPU usage is the half of the previous, is there anyone can tell me why? Thank you!
View 1 Replies View RelatedSQL Resources
May 8, 2006Hi everyone!Being new the SQL, I wanted to ask everyone if there are good resources out there that they use? I could use all the help I can get! Books, links, websites, etc.Recently my friend sent me this, I found it informative and best of all, free! https://store.pentontech.com/index.cfm?s=9&promocode=qe2165f7
Thanks in advance for your help!- Lizzy
Resources
Jul 5, 2001Sometimes logging in to SQL 7 I get 'Memory resources not available...' and
log in process terminates. Do I need to up number of locks !
If so, how ! Pls. suggest any other ideas !
I have 2g ram, 4 CPUs NT 4 SP5
Thanks,
Rick
What Is Using Resources...now!
May 5, 2006This proc will show you what is using SQL system resources right now. If a process is not currently using any resources, it will not show up on the list. Also, a dump of the input buffer is printed so you can see just what is going on.
I use this code whenever somebody whines that "The server is slow!".
The magic is ::fn_get_sql which came with service pack 3 for sql 2000.
use master
IF (object_id('sp_Now')) is not null
BEGIN
PRINT 'Dropping: sp_Now'
DROP PROCEDURE sp_Now
END
PRINT 'Creating: sp_Now'
GO
CREATE PROCEDURE sp_Now
as
-- FULL DETAILS OF WHAT IS RUNNING
set nocount on
declare @handle binary(20),
@spid smallint,
@rowcnt smallint,
@output varchar(500)
DECLARE TEST CURSOR FOR
select sql_handle, spid
from sysprocesses
where sql_handle <> 0x0000000000000000000000000000000000000000
-- and spid <> @@SPID --> Uncomment to stop seeing your own process
order by cpu desc
OPEN TEST
FETCH NEXT FROM TEST
INTO @handle,
@spid
set @rowcnt = @@CURSOR_ROWS
print '===================='
print '= CURRENT ACTIVITY ='
print '===================='
print ' '
set @output = 'ACTIVE SPIDS: ' + convert(varchar(4),@rowcnt)
print @output
WHILE @@FETCH_STATUS = 0
BEGIN
print ' '
print ' '
print 'O' + replicate('x',120) + 'O'
print 'O' + replicate('x',120) + 'O'
print ' '
print ' '
print ' '
select convert(char(15), loginame) as 'loginame',
convert(char(10),hostname) as 'hostname',
convert(char(20),db_name(dbid)) 'database',
str(spid,4,0) 'spid',
str(blocked,5,0) 'block',
-- str(waittime,9,0) 'wait_time',
str(physical_io,8,0) 'phys_io',
-- str((cpu),10,0) 'cpu(ms)',
-- str((cpu/60000.0),9,3) 'cpu(mins)',
str((cpu/1000/60),6) + ':' + case when left((str(((cpu/1000) % 60),2)),1) = ' ' then stuff(str(((cpu/1000) % 60),2),1,1,'0') else str(((cpu/1000) % 60),2) END as 'cpu(mm:ss)',
str((convert(float,memusage) * 8192.0 / 1024.0 / 1024.0),8,2) 'mem(MB)',
convert(char(30),program_name) as 'program_name',
cmd,
convert(char(15),lastwaittype) 'lastwaittype',
convert(char(20),login_time,120) 'login_time',
convert(char(19),last_batch,120) 'last_batch',
convert(char(10),status) as 'status',
convert(char(15),nt_username) as 'nt_username'
from master..sysprocesses
where spid = @spid
print ' '
print ' '
dbcc inputbuffer(@spid)
print ' '
print ' '
select * from ::fn_get_sql(@handle)
FETCH NEXT FROM TEST
INTO @handle,
@spid
END
close TEST
deallocate TEST
GO
IF (object_id('sp_Now')) is not null
PRINT 'Procedure created.'
ELSE
PRINT 'Procedure NOT created.'
GO
SQL Resources
Jul 31, 2006Hi,
Im just looking for someone that has a demonstration Visual Basic database script.
Something that makes a database, ad / removes rows and information, does all basic database stuff, just as an example of how a SQL database is supposed to be handled.
Anyone have any links?
Resources Used By UDF
Jul 23, 2005I know that a query inside a called UDF does not show up when displaying theestimated query plan, but are the resources (CPU, DiskIO) used by a UDF thatis call from within an SProc or embedded in a SQL statement included insp_who2 and Profiler BatchCompleted?Also how performance draining is it to do something like the following. Iassume for every row returned the udf will be called causing 8000 tablescans on DateTable?Create Functionu udf_GetCountDates (@StartDate datetime, @EndDate datetime)Returns IntbeginReturn(select count(*) from DateTablewhere ColDate > @TestDate and Col1Date <)end--DateTable has 5,000 rowsCreate proc Test@TestDate datetimeAsselect Col1, Col2, udf_GetCountDates(@TestDate, Col2)From OTable--OTable has 8,000 rowsGoThanks,Ray
View 1 Replies View RelatedResources
Jul 20, 2005I have a question...say I have an asp app that works as a front end toan sql server 2000 database. Would it be better, performance wise, toseperate the webserver from the sqlserver or to have them on the samemachine?-Jim
View 2 Replies View RelatedIndentifying How Much Resources Are Used
May 13, 2004I have a sql job(that runs several stored procs) that is run to fix a problem, it is set to run every few minutes (no, this is not my idea). What would be the best way to identify how much this specific job/and it's sp's are using in resources, such as cpu and memory?
View 1 Replies View RelatedResources For MS Certification
Jun 19, 2002I was thinking of doing certification from Microsoft. I was first intending to write exam 70-229 which is :
Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition.
The Microsoft site http://www.microsoft.com/traincert/exams/70-229.asp#TOOLS has a list of topics that I need to be familiar with in order to pass the exam, but it does not have the detailed information.
Can someone guide me to a place where I can go to get the detailed information about these topics so that I can prepare better for the certification exam?
thanks
Aslam
Mssql Resources
Mar 9, 2006Hi folks, thought I'd wade into the mssql waters, I've used mysql for some time, thought I'd familiarize myself with the other.
I downloaded mssql server 2005 express edition. Don't know if I missed anything on the site, maybe I did. I've got it running from the command line.
Are there any GUIs out there that are decent to use? Where can I download it?
Is there a specific manual available like mysql has? I think I stumbled across something but wasn't sure it was searchable, sorry the link is on my other 'puter at the moment so I can't see what I was looking at.
perhaps a sticky in this forum with good resources would be helpful to all.
Thanks,
Dave
Point Me To Some Resources
May 21, 2008I normally develop for MySQL and PHP, but we have a new application that runs on MS SQL and I'm having difficulty using the same structure as MySQL to run queries. Can someone here point me to a good resource to learn the basics?
Thanks!
Limiting The Resources
Jun 13, 2007Hi
I have a problem on microsoft sql server 2000.
I need to limit the amount of resources(eg cpu, mem..) a query uses
Is there a way to do this in SQL server 2000
Thks in advance
:eek:
NOLOCK And No Resources?
Mar 31, 2008What will happen if I'm running a query with the NOLOCK hint but the SQL Server has run out of resources?
Thanks!
Using Resources With SSIS
Apr 2, 2008
Hello,
There are many Errorcodes which SSIS uses to represent errors, warnings etc.
List of errorcodes are at: http://msdn2.microsoft.com/en-us/library/ms345164.aspx
Now, in my SSIS package, I have defined error handler(OnError) and warning handler(OnWarning) to handle the events. Which logges them to a flat file with some formatting and condition checks including addional information about faulty row.
I dont want to log the exact error messages sent by SSIS as they are too much technical and not understanadble by normal user. I want them to be replaced with some custom message which I 'l pick from some resource file by matching the ErrorCode. One can consider this resource file as collection of key(ErrorCode), value(Custom Error Message) pairs.
In .NET we normally use resource files(.resx) to achive the same task. I found no way to use such resource files in SSIS.
Is there any way by which I can implement this functionality?
I dont want to implement custom logging mechanism as it does not fit into my application needs.
Please guide.
SQL Is Using Up All My System Resources
May 8, 2007
Hi
SQL Server is using up all of my system resources and i can't find where the problem is, i have used
exec sp_who
exec sp_who2
but i don't know what to look for, i'v even checked the memory comparison according to SQLSkills but that is all fine, the server is extremely slow all the time. I battle to open Query analyser or even just to connect to it takes forever, any advice would be greatly appreciated.
Kind Regards
Carel Greaves
Learning Resources
May 8, 2006could we download the videos of SQL Express learning resources as we can do it with the VWD, VB, C#????
there is another link to do it???
will they be available in a short future?
Deadlock - Which Resources Are Really Deadlocked?
Apr 10, 2000The errormessage after a deadlock gives very little information: "Your transaction (process ID #12) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction."
But what resources, ie tables, indexes etc, are really deadlocked?
Is it possible to set a switch or something to log all relevant information about a deadlock for later investigation?
Performance Tuning Resources
Jun 13, 2000Anybody have a suggestion on text books or other materials that deal with performance tuning in SQL 7.0. From what I have seen SQL 7.0 is far diffrent than 6.5. I'm specificaly interestd in information about query plans, profiler and what a table spool does.
View 1 Replies View RelatedResources On Security Implementation
Jun 9, 2000Hi,
a) Thanks for all contributions on this site, they have been very useful but,
i am trying to implement a security procedure including auditing on sql server 6.5,7, sybase and oracle can anyone help?
b) does any one know any site like this that covers issues on oracle ( besides Oracle Technet)?
Data Warehousing Resources
Sep 20, 2002Hi:
I am planning to give the microsoft 70-019 data warehousing exam. I am not able to find any good resources to prepare for the exam. Please help!
Thanks in advance
SQL Server Deadlock On Resources??
May 30, 2007We've got a 3rd party application that periodically runs SQL commands throughout the day. We've been getting issues with this application showing a sql error:
Transaction (Process ID 71) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
In checking the processes on SQL Server, there were a lot, and process ID 71 was actually hitting a completely different database.
Is there a way to streamline how SQL Server handles processes, and what's the limit at any given time?
SQL 2005 Express Resources
Jun 9, 2007Will I am running SQL 2005 Express on windows 2003 sp1 with a P4 3.4Gh (with hyperthreading), 2GB ECC RAM.
I am running software that connects to SQL and reads/saves data.
One of the programs which is CPU hungry manages data and the other manages the SQL connection and reads/saves the data.
My problem is that I am recieving an error that usually means connection to SQL is lost, this tends to be because the CPU hungry application is denying SQL from functioning.
However the annoying thing is the CPU use rarely passes 45% and in a another test environment never passed 5%. RAM is the same, consumption is less than 35%.
Please advise
Thanks a lot!
Limit Resources Per User
Feb 15, 2006Is there some kind of way to limit the resources (CPU, number of sessions)per user in MS SQL 2000 database.Some kind of resorce manager thing...
View 1 Replies View RelatedWhat Resources Does Truncate Table Use
Jul 20, 2005Hi,We have a heavily used production server and a table which logs everyhit on a web site. This table has grown large over time and we want toclear it down as efficiently as possible. We would like to issue atruncate table statement, but with millions of rows we are a bit waryof how this will affect server performance. The alternative is todelete is stages using rowcount but of course this will generate alarge amount of logging.Can anyone confirm whether issuing a truncate table will have anyaffect on performance - i.e. does it just clear a header block ordooes it have to do more?CheersMartin
View 3 Replies View RelatedResources For ABSOLUTE Beginners
Jan 26, 2007Seems to me like microsoft mentions learning, but it's never for beginners. I haven't found a single tutorial that says how to make a simple database step by step.
Does anybody know about a tutorial focused on teaching the very basics (a kind of "hello world" application) from setting up the new database with a few sample fields to "deploying" it (making it ready to be used)?
Please let me know.
Once I'm through with these first steps there seems to be a lot of helpful learning resources meant for experienced and expert users, but I'm not there yet...
Thanks,
Bernardo
How Can Internal Activation Use More Resources?
May 5, 2007Hi,
I'm using service broker queue with internal activation to run a stored procedure.
The DB server is windows 2003 R2, 4 cpu, with SQL server 2005 SP2.
When I'm runing the stored procedure directly from the sql management studio it takes about 75% of the cpu and running for about a minute, but when the stored procedure is activated by the queue internal activation (as a background process) it uses only 25% of the machine cpu (my guess it uses only 1 cpu insted of all 4 cpu) and running for much longer time (sometimes even more than one hour).
How can I change this behavior? I want it to run as fast as possible.
The queue decleration is:
CREATE QUEUE [TaskQueue]
WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = ProcessTasksProc,
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF);
Thanks in advance,
Shai Brumer
Reporting Services Resources
Apr 4, 2008
I'm new to this forum and have just started working with Reporting Services. I was wondering what were some good resources to help.
Thanks
Resources In CLR Stored Procedures?
Jul 15, 2006Can Resource Files (containing, for example, string resources holding the text of exception messages) be part of a .NET project holding Sql Server CLR Stored Procedures?
Thanks for you help,
Andy