I've got a new server with Pentium4 xeon processors. I've used sp_who2
for quite a while on my old server -- to look at CPU and IO usage. On
the new server, the CPU times are huge -- although actual performance
is quite good. I know sp_who2 is an undocumented proc. Has anyone
experienced this or is it just me?
I have the SQL code for one of the select list columns as shown below in the SSRS Data set query level.
 Example : Select IsNUll(max(ET.[# Utilizations by Utilization Method]),0) as [Employee Period Total]
SQL Result Set Output :
0
0
0
The above code when run in SQL displays correctly with zeroes if NULL values are available from the Source tables.Now at the SSRS Report level design I have grouped with more than one values.Even at the expression  I have given  =Fields!Employee_YTD_Total.Value but report output displays blank In order to evaluate 0 from the SQL result set I even tried equating the values to 0 like = Iif(Fields!Employee_YTD_Total.Value = 0 ,0,Fields!Employee_YTD_Total.Value) but still the report outputs is blank.
Issue/Query : Why it displays blank and I should have this as 0 in the report output to eliminate blank as this would be not appropriate to the end users to validate. What has to be changed either at the SSRS level or at the SQL level.
I've created a report that has cells in it that I want to be formatted as percentages. I set the format code in the cell properties to "P1" and everything appears to work fine until I export to Excel. Some cells show up with only one digit after the decimal and some show up with two digits after the decimal.
I looked at the formatting in Excel (right click, Format Cells...) and it is set to "[$-1010409]#,##0.0#%". I don't have the first clue what that is "supposed" to do but what it does is this:
If I enter 100.00 in the cell it shows up as 100.0%. Expected
If I enter 100.10 in the cell it shows up as 100.1%. Expected
If I enter 100.15 in the cell it is displayed as 100.15%. Not expected
I have formatted the report to only show one digit after the decimal, but RS exports to Excel with some funky formatting that sometimes shows one digit and sometimes two digits after the decimal. Why wouldn't it simply format the cell as a percent? How can I get it to only show a single digit after the decimal?
This is Excel 2007 but Excel 2003 has the same behavior.
Thanks
--John
providing 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?
Whe we execute sp_who2 on a SQL server
Command column displays the following:Any idea on what is CONDITIONAl??
SIGNAL HANDLER
LOCK MONITOR
LAZY WRITER
LOG WRITER
CHECKPOINT SLEEP
AWAITING COMMAND
BULK INSERT
CONDITIONAl
Thanks.
Di.
sp_who2 shows the block by user and some information.Currently we are moving from sql 2000 to sql 2005. if i execute the sp_who2 in sql 2005 it shows only my login block information. it does not show other users. is there any admin rights has to give to view other block user ??? how to do that.
View 7 Replies View Relatedusing openrowset for querrying, the result displays ?????????? instead of column name .My column name is in arabic which is a alias name.
Where as i am able to insert data in arabic and i can retreive the data in arabic from my table.
The problem exists with alias names given for my column.My columns names are displayed as ???????? in stead of arabic names
DiskIO: Any idea what exactly this tells on sp_who2 output? Does that men anything if its high/low?
Thanks.
Ravi.
Is there any way to calculate (just an approximation is fine) theamount of work that is done for an insert statement?For example, can I calculate the approximate row size of my table andthen compare that with the DiskIO for my SPID to determineapproximately how many rows have already been written? Or, does theDiskIO include shuffling data around or other DiskIO that makes thiskind of comparison impossible?Thanks,-Tom.
View 1 Replies View RelatedThanks, folks, for taking time to help!In query analyzer and profiler, there seem to be system processes thatare generating high cpu time and disk io. Well, I guess the short ofit is that I am having a problem interpreting what I am seeing so thatI can take the next step in corrective action. There are also severaldomain controlled logins that appear to be on the same host name, atthe same time--these are laptops, not terminal servers--why would thesystem be reporting such a thing?Here is a sample of the output from sp_who2:SPIDSTATUSLOGINHOSTNAMEBLKBYDBNAMECOMMANDCPU TIMEDISKIOLASTBATCHPROGRAMNAME1BACKGROUND sa . . NULLLAZY WRITER75006/3/2005 5:142sleeping sa . . NULLLOG WRITER1025006/3/2005 5:143BACKGROUND sa . . masterSIGNAL HANDLER1606/3/2005 5:144BACKGROUND sa . . NULLLOCK MONITOR20306/3/2005 5:145BACKGROUND sa . . masterTASK MANAGER01516/3/2005 5:147sleeping sa . . NULLCHECKPOINTSLEEP243822396/3/2005 5:1410BACKGROUND sa . . masterTASK MANAGER0256/3/2005 5:1411BACKGROUND sa . . masterTASK MANAGER01636/3/2005 5:1412BACKGROUND sa . . masterTASK MANAGER0706/3/2005 5:1413BACKGROUND sa . . masterTASK MANAGER01556/3/2005 5:1451sleeping saMyServer . msdbAWAITINGCOMMAND683371386/8/2005 13:00SQLAgent - Generic Refresher52sleeping saMyServer . msdbAWAITINGCOMMAND1746266/8/2005 13:00SQLAgent - Alert Engine53sleeping DomainUser1LT1 . VPNMasterAWAITING COMMAND1687546/8/2005 12:31Crystal Reports54sleeping saMyServer . VPNMasterAWAITING COMMAND377296/8/2005 10:49MS SQLEM55RUNNABLE DomainUser2MyServer .masterSELECT 86006/8/2005 12:40SQL Profiler56RUNNABLE DomainUser2LT2 . VPNSELECT34496/8/2005 12:50Microsoft® Access57sleeping DomainUser3LT2 . VPNMasterAWAITING COMMAND110296/8/2005 12:52Microsoft® Access58sleeping DomainUser1LT1 .masterAWAITING COMMAND1172156/8/2005 9:31Crystal Reports59sleeping DomainUser4LT2 . VPNMasterAWAITING COMMAND1516/8/2005 12:50Microsoft® Access62sleeping DomainUser5LT2 . VPNMasterAWAITING COMMAND3226/8/2005 12:52Microsoft® Access63sleeping DomainUser6LT2 . VPNMasterAWAITING COMMAND50106/8/2005 12:52Microsoft® Access64sleeping DomainUser6LT2 . VPNAWAITINGCOMMAND101606/8/2005 12:52Microsoft® Access65sleeping DomainUser6LT2 . VPNMasterAWAITING COMMAND47496/8/2005 12:52Microsoft® Access66sleeping DomainUser3LT2 . VPNAWAITINGCOMMAND20306/8/2005 12:50Microsoft® Access67sleeping DomainUser6LT2 . VPNAWAITINGCOMMAND24906/8/2005 12:52Microsoft® Access68RUNNABLE saMyServer . masterSELECTINTO 62126/8/2005 12:59SQL Query AnalyzerThanks,Eric
View 2 Replies View RelatedThe behavior seems to have changed on SP_WHO2 in SQL 2005.
In SQL 2000 I could be logged in as a regular user in my system and run SP_WHO2 and get all the users currently logged in. This is no longer working in SQL 2005. It now only returns "me".
What kind of rights do you need to have to see who is logged in?
In the sql server analyzer, i ran sp_who2 store procedure to find out the procesess and locks on the sql server. I noticed on the status column some entries "RUNNABLE", does this could trigger blocked to other processess if not closed by our application? or What does runnable status mean?
View 1 Replies View RelatedDear All,
i've a doubt regarding the difference between sp_who and sp_who2.
i've read in one article stating that sp_who is documented and sp_who2 is un documented.what is meant by this?
and please provide me the articles related to locks(what are different types of locks)
thank you very much
Vinod
Even you learn 1%, Learn it with 100% confidence.
Hello,
When I run sp_who2, the blkby col shows spid -2.
From doing some reading I understand this is caused by SQL waiting on MSDTC.
If I look at MSDTC running transactions I can see 2 transactions, the transaction are not mark as indoubt so I cannot commit, abort or forget them.
If I stop ALL the Processes that are using the DB, eg IIS and a 3rd party workflow engine, these transactions do not go away.
Anyone got a clue?
If I restart MSDTC, the blocks go away in SQL but this is not a solution to the problem.
All MSDTC gives me is the ID for the transaction, can I get more info on whats happening from this ID.
Thanks
Steve
SQL Server 2000 documentation says that the syproperties value (via sp_addextendedproperty) has a limit of 7,500 bytes. So I have a property (MS_Description) on a column which is 654 characters long. When I run fn_listextendedproperty or if I select value from sysproperties I get only the first 256 characters returned. As if the field was truncated when I added it. BUT, if I select something like right(cast(value as varchar(1000)), 200) from sysproperties, I see the remaining characters. So they are there but I can never return the entire string at one time.
Any ideas why this is and how I can get around it?
Hi!
I want to extract the same information as the Activity Monitor does with SQL in SQL Server 2000.
Have tried to use sp_who2 but I miss some important columns.
How do I do?
Thanks!
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
Issue:
SQL Server 2005 Management Studio displays the old filename in the tab at top if you open a .sql file and then do "file save as". I have upgraded to SP2 in hopes that this behavior would be corrected but to no avail.
We have 2 users accessing exactly the same reports/data but who are getting different numbers of pages printed.
The report viewer displays 12 pages of data; when one user selects Print preview they get 12 pages displayed and 12 pages print. When the other user displays exactly the same 12 pages of report data and preview it, they get 24 pages, with the last column apparently spilling on to a second page. I realise that physical pagination is controlled by the print control and is unrelated to the HTML rendering, but the users have the same printer selected and the print-preview shows the margins set to the same size. What else can be causing the difference?
We are using RS 2000, SP2. TIA for any assistance with this.
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?
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!
Is it possible to alter the format/layout of the parameter input boxes that the user enters values into.
reporting services automatically arranges them. Is it possible for the report writer to set the layout?
I know that when using report viewer in asp.net that the properties of the viewer are confiigurable.
Hello,
I'm using SQL service Broker 2005 with ASP.NET 2.0 in order to use the sql cache dependency.
everything works fine...
I have only a doubt regarding a query that is constantly executed on mu db ( i can see it be means of the SQL Profiler)
The query is:
exec sp_executesql N'BEGIN CONVERSATION TIMER ("'') TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name, conversation_handle,
cast(message_body AS XML) as message_body from [SqlQueryNotificationService-GUID]), TIMEOUT @p2;',N'@p2 int',@p2=60000
The web application is not running from a browser ...
It this ok or I forget to clean/reset something from my web application and/or sql server?
Thank you
Marina B.
I need to create a basic report that displays the userid, username, email, status and need to update the status column to enabled when we select any particular username. And the username parameter comes from the available users from the database. How to do this?
Do I need to mention the where clause where username=@username in the query or we don't need any
Also I need to get the available distinct usernames from the database while entering the parameter username
/*DECLARE variables to use IN queries*/
DECLARE @intErrorCode INT
DECLARE @ErrorMessage VARCHAR(MAX)
DECLARE @UserID INT
SET @ErrorMessage = 'Completed.'
SELECT [userid] ,
[code].....
Basically first I need to find the userid, username, accountstatus, email for a given particular user. Then I need to update the accountstatus to enabled for that selected user.
Also I need to create auditing that from before update and after update the accountstatus.
Hello.
I am using a SQL Server 2005 devenv and each time I open my ssis
package, the server mem usage jumps up to almost 400mb ram. On opening,
tries to validate each data flow task and that takes forever, I have
delayed validation however still takes time to load. I am currently
using terminal services to access visual studio and the project has two
ssis packages that myself and another developer are working on, could
this have something to do with this?
In saying this, everything worked perfectly a week ago, now its
extremely frustrating.
Anyone with feedback would be greatly appreciated.
Cheers
I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.
SQL Server works fine when connecting to it using Mangement Studio on Windows XP.
However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).
When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.
The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.
The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.
On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).
Any ideas?
hey guys,could anyone help me out ..?i have a serious problem with my SQL server DB ..it uses up all my memory (i see it in task manager) after a whole day of asp's triggering the db .. it should go down when there are no triggers anymore, right?please help :)Stallema
View 4 Replies View RelatedHi,
One of the production box running only sql server application, is showing 80% memory usage on the task manager-memory usage history right now.
We are running sql server 2000 standard version-sp3 with 2GB memory on this box. Server is not on the scheduled reboot at this point.
We have seen this behavior for this box last month that after task manager showing 90% memory usage contantly for several days, when server was manually rebooted, memory usage dropped to 35%. Now it's back to 80%.
Our DBA thinks that server should be rebooted on a regular schedule regardless of memory problem. Our network admin doesn't seem to agree with this. He is not ready to reboot the machine even with this high memory usage.
There is no noticable difference performancewise yet.
My questions are:
Is it bad that memory usage reaches from 35% constant to 80-90% or is it common? Should sql server be rebooted immediately to take care of it? Should sql server 2000 rebooted on regular basis regardless of any problems? Shouldn't sql server be releasing memory back to the OS even without rebooting? How do I find out whether server actually is going through memory problems and what is causing it?
Thanks in advance for your opinions,
Shaili
Has anybody encountered SQL SERVER 6.5 using 100% server CPU at startup with no other processes running on the NT server or accessing SQL Server?
I have tried starting up in minimal config mode to access the config values, but am having SQL Server hanging by that point as CPU usage has hit 100%
If anybody knows an answer please let me know.
Thanks
Hi All,
Currently I have an application that uses SQL 2000. The SQL server service tends to take up as much of the physical memory as possible. The problem is I also have other services relating to this application running that are very important.
What tends to happen after a period of time is SQL takes up all of the physical memory, so that the other services are using the paging file (virtual memory). This causes extremely slow response time over the network as these other services are having to parse the paging file.
Upgrading the memory is currently not an option :(
I know there is an option to set memory usage for SQL but I am unsure how this would respond in a production environment. What would happen if SQL would require more memory than what was allocated to it?
Can SQL release the memory and still act as normal?
Any input would be generously appreciated
Cheers!
Hi,
For the last week, our production SQL server is running very slow and causing the CPU usage to go 80-100 % almost all the time. This causes certain queries to time out. Our application has never timed out before ever. Also, we did not do any updates on our production machine or installed anything recently.
Has anyone of you ever experienced this issue? If yes, then what did you do to resolve it? any help would be greatly appreciated.
Thanks for your help!
I am using SQL Server 7.0 on a Windows NT machine. We have been having problems with SQL Server not releasing the memory after it has utilized it.
Currently it is configured to allow a max of 511MB memory (1024 is total on the machine). I had some advice that my best solution would be to reduce the Max Memory to a lower value (say 400 MB) to help reduce the problem.
Is this not counter-intuitive? Or is this the correct solution?
Thanks.
Hey Guys - I was just wondering - if at work you have started to use Linux Servers - and if so, which Distribution of Linux and for what purpose. Cheers - Mark Smith
View 1 Replies View Related