Index Utilization

Mar 29, 2001

SQL 7 SP2

Hi.

True or false on these:

1. A table has a PK of EmployeeID (non-clustered). The sql statement where clause uses something like "WHERE E.Action > 1 AND E.User = 1001 AND E.EmployeeID = 12345
Question: Will the PK index be used in determining the result set ?

2. A table has an index of EmployeeID + Company + State (clustered). The sql statement where clause is "Where EmployeeID = 1001".
Question: Will the index be used in determining the result set ?

Thanks,

Craig

View 1 Replies

Making A Query Have Flexible Index Utilization

Apr 29, 2008

I have one query that looks like this.

DECLARE @propname

SET @propname = NULL -- can be Null or have wild card values

SELECT col1, col2, col3, propertyname FROM testtable where col1 = 1 and col2 = 2 and col3 = 3 and (@propname IS NULL OR propertyname LIKE @propname)

col1, col2, col3 are part of a clustered index

propertyname is a nonclustered index

This is the predicament. If the "@propname is NULL" is first in the OR statement then the query will use the clustered index for finding the record. If I put "@propname is NULL" last then it uses the propertyname index no matter what. So I either get full index scans if NULL is ever used on property names or I get consistent two second long searches on the clustered index. Any way to have the best of both worlds? Or do I have to divide up my query into more stored procedures?

THANKS

View 11 Replies View Related

Limiting Disk Utilization For Clustered Index Creation

Aug 28, 2001

Hi,
Does anyone have any good tips for reducing the amount of disk space required for new clustered index creation.

I have just finished building a rather large table (48gbs) and durring the clustered index creation I came very close to running out of disk space.

What I am already doing:
1.Recovery Model simple
2. index padding 0
3. fillfactor 0

Any help would be appreciated
daLoonster

View 1 Replies View Related

Difference Between Index Seek && Index Scan && Index Lookup Operations?

Oct 20, 2006

please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio

thank you in advance

View 3 Replies View Related

CPU Utilization

Jul 16, 2001

Hi All,
We have two production boxes running different ERP Apps on each boxes, We just added 2 more cpu's to one box and 1 cpu to other. The Currect CPUS config as stands is

Box 1 = 4 CPU's
Box 2 = 2 CPU's

My boss whats some stats to see if performace was any better and if yes how much !!

How can i find such information from my box ?

Can anyonw help with this stat's ? !!

Thanks in advance

Girish

View 3 Replies View Related

CPU Utilization

May 2, 2005

Hi all,
I have a problem in trying to find out why only one CPU in a 2 CPU H-T utilized. Using task manager, I can see 4 processors windows but only 1 actually utilized. I select +boost sql server and select all CPU for use. Queries and all other command ie dbcheck.. appear to use only single cpu.
Any help would be nice.
thanks
Andrew

View 4 Replies View Related

CPU Utilization

Feb 12, 2004

Hi All,

I want to keep track of the CPU utilization & number of users connected for each database on our production box. I chose to get the data from sysprocesses table from master database.

But I realised that for some reason the master..sysprocesses.CPU column stays static or just keeps on adding to existing values.

Is there any ways thru which I can clear this data ( cpu column in sysprocesses table) after I have captured it in a table ?

Any help is appreciated.

Thanks.

View 1 Replies View Related

SQL CPU Utilization Goes To 0

Jul 20, 2005

We have a SQL 7.0 Standard Server running on a Windows 2000 Servermachine with 2 800mhz Pentium III with 2GB memory. Our front end isAccess 97 and 2000 with most ADO connections for the scripts but someDAO for forms and reports. We recently "released" a new version ofthe "database" that caused a catastrophic event to start happeningwith our SQL server.Using PerfMon we monitored the CPU utilization on the server andnoticed that the CPU load would drop to 0 for approx 5-10 seconds andthen jump back up to our average 60-70% utilization. During thisdrop, there is NO disk activity no new connections being made, etc.We then took the process a step further and loaded a "stress" programthat put about 30% load on the server to start with. Then wemonitored each processes load. SQL Server process would drop to 0%while the stress process continued at 30%.The problem is that the SQL does absolutely NOTHING for 5-10 seconds.You cannot connect, any querys that are running stop, their is no diskactivity (logs, data drives), and you cannot even get sp_who2 to runfrom Query Analyser. We thought maybe blocking (we have built an"app" that monitors this), but we don't see any blocking before itlocks and nothing after it locks.Out of despiration we "rolled back" to our previous version to getpeople working again. After business hours, we have tried toduplicate the problem on machines (2 or 3 at a time) but cannot get itto duplicate the problem.The only experience we had previous to this was using DNS to resolvethe server name which caused a problem EXTREMELY familar to thisproblem. However, we have double checked every machine we have, andnone of them are using DNS to resolve.Any idea's would be most appreciated.Patrick Moore

View 2 Replies View Related

Memory Utilization

May 10, 2000

I installed 4GB of memory and I have never seen SQL memory utilization go beyond 2GB. I have SQL server set up to use as much memory as it needs. Does anyone no if SQL server can make use of more than 2GB.

View 1 Replies View Related

Memory Utilization On SQL 7.0

Feb 28, 2000

Ours is a SQL 7.0 Enterprise edition with NT 4.0 Enterprise Edition. SQL Server has been configured with the default, 'Dynamic memory Allocation'. The system has 4GB of RAM. This is a dedicated SQL Server machine. But SQL Server seems to use only 1.8GB RAM(Counter: Total Server Memory) The page faults seem to be a max. of 600 and an avg. of 100. The processor utilization has suddenly increased to 90%. Is there anything wrong with the way SQL server is using memory? Is is not true that SQL Server 7.0 Enterprise edition can use upto 3GB RAM in a 4GB system?

Are there any links that can help troubleshoot this problem?

Thank you.
-Praveena

View 2 Replies View Related

High Cpu Utilization

Mar 21, 2000

After a fresh install of SQL 6.5 with SP5a(or without), the cpu is running at anywhere from 50%-80%. It is loaded on a PDC, but when I stop the sql service cpu utilization drops to 0-2%. When I start the sql service it's right back up there, does anyone have a suggestion as to how to fix this or why the service would be doing this?

Thanks, Christel

View 1 Replies View Related

High CPU Utilization !!!! Help !!!!!!!

Sep 5, 2002

I am getting high CPU utilization on the SQL Server process (>90%).
However the overall utilization (NT -- entire box) always seems to be under 50%.

Can someone explain why this is happening. The server is a quad; the SQL server process seems to be using only two CPUs at a time (not the same ones all the time).

Lightweitht pooling has been turned on and the maximum worker thread size has been left at the default value (255).

How can I configure SQL options to spread the load across all four CPUs ??????

View 2 Replies View Related

Problem With CPU Utilization

Sep 6, 2006

I have windows 2003 server with SQL Server installed on it for live calls billing but the CPU utilization is reaching the maximam and it's average above 60% which is causing lot of problems specially for the live environment. I have enough memory and free hard disk space is more than 40GB,

so where is the problem?!

View 5 Replies View Related

Memory Utilization

Jul 20, 2005

What do people think is normal for memory utilization? I know that's toobroad, so here are some basics.MS SQL Server 2000, Windows 2000 Server, 2GB RAMDb 1, size = 2.0 GBDb 2, size = 300MBDb 3, size = 50MBDb 4, size = 30MBDb 5, size = 30MBTypically 4-6 users, moderate usage 8-hrs/day. Performance has not slowed.Reboot on Sunday. sqlservr.exe in the Task Manager reports the followingSun 61MBMon 200MBTues 800MBWed 1,124MBThu 1,424MBFri 1,303MBI was getting srv 2020 errors when I had just 1 GB RAM: "The server wasunable to allocate from the system paged pool because the pool was empty."Then I did several updates to address this and got more RAM. I haven't seenthe errors since, but I haven't waited for them to happen: I'm rebootingevery week now. The memory numbers make me suspect SQL Server.Scratching my head. Not sure if my problem is gone, and this is normal SQLServer 2000 behavior, or if my problem is still lurking and I've only mutedit a bit.Any thoughts greatly appreciated.Tom

View 3 Replies View Related

Problem In CPU Utilization

Sep 6, 2006

View 4 Replies View Related

Memory Utilization

Apr 19, 2007

Dear all,

One of the server is having 2 GB of RAM and task manager is showing 1.87 GB memory in use.

I have to migrate few databases on the same server.

With high IO Operations.

I know server require more RAM, but how can i prove that server needs more RAM ?

Regards

Mohd Sufian

View 3 Replies View Related

High CPU Utilization

Oct 17, 2006

Could anyone help me in finding out why the cpu utilization is very high??

I have two servers say, Server A , server B. There is a transactional replication going on from server A to B

There is a table say Table A on server A, which is being replicated to server B.

I created a trigger insert and update trigger on Table A on server B (i.e. on subscriber). Since then, the CPU utilization for server B is very high 80-90%

when i used profiler, i could see .whenever replication stored proc for insert or update executes..cpu utilization goes up..

trigger just insert the updated/inserted rows into some other table.

Could anyone tell me why the cpu utilization has gone up so much?? i am using sql server 2005

thanx

View 1 Replies View Related

DMV's For Capturing CPU Utilization

Jun 3, 2008

Hi

Please let me know which DMV is best to cpature total system CPU utilization. There are plenty of views so i am a lil bit confused

thanks in advance,

View 3 Replies View Related

High Memory Utilization

Sep 27, 2001

I have a few in house developed application (VB based) that access the SQL server for adding, appending , creating tables. The application does the changes thru queries dynamically generated at the application level.

My MS SQL Server runs on a PIII / 256 MB Ram / 18 GB HDD

The problem is that the memory utilization of SQL server keeps growing constantly. Out of 512 MB (256 Physical + 256 Virtual) available teh memory utilization reaches a level of 490 MB and statys constant. Though SQL Server shows a utilization of 150 MB.

I suspect that SQL is not releasing memory back to the system. Please help in resolving. The problem may lie at the applications developed.

Jdindian

View 2 Replies View Related

Database Processor Utilization

Jun 13, 2007

Is it possible to know the processor utilization and memory usage of a single database in a SQL server?

View 1 Replies View Related

Indexed View Utilization

Aug 1, 2006

I created an indexed view in SQL 2000, and I expected to see the index created on the view referenced in the execution plan when I query the view. Instead, I see the index for the base table referenced in the execution plan. Why?

There are 6,000,000+ records in the base table, and the view only references 256 of these rows.

Here is some of the DDL if you need it:

CREATE TABLE [alarm_t] (
[ct_dtm] [datetime] NOT NULL ,
[dst_flg] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[stn_nm] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[alarm_txt] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[utc_dtm] [datetime] NOT NULL ,
[create_utc_dtm] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [alarm_idx2] ON [dbo].[alarm_t]([ct_dtm], [stn_nm], [dst_flg]) ON [PRIMARY]
GO

create view dbo.alarm_Mapbd_v with schemabinding
as
SELECT
[ct_dtm],
[dst_flg],
[stn_nm],
[alarm_txt],
[utc_dtm],
[create_utc_dtm]
FROM [dbo].[alarm_t]
WHERE[stn_nm]= 'Mapbd'
GO

create unique clustered index alarm_Mapbd_idx1 on dbo.alarm_Mapbd_v
( stn_nm, ct_dtm, dst_flg )
go

update statistics alarm_t
go
update statistics alarm_Mapbd_v
go

The following 2 queries have the exact same execution plan, both showing a cost of 50%. I expected to see the index created on the view referenced in the execution plan for the first query. Is the index created on the view being used?

selectstn_nm, ct_dtm, dst_flg
fromalarm_Mapbd_v
go
SELECT
[ct_dtm],
[dst_flg],
[stn_nm],
[alarm_txt],
[utc_dtm],
[create_utc_dtm]
FROM [dbo].[alarm_t]
WHERE[stn_nm]= 'Mapbd'
go

Thanks for your assistance.

Tom

View 1 Replies View Related

Qry Analyzer: Processor Utilization

Jan 30, 2004

I have noticed that when using SQL Query Analyzer some of my queries will use 100% CPU on my PC and next to nothing on the SQL server, while other Queries require 100% CPU on SQL server and do next to nothing on my PC. Does anyone know what determines this??

Right now I can produce this by executing two very similar T-SQL selects. The one that runs on the server only has one additional join - a very simply join at that. If I can change my SQL to make it run client side in some situations, that would be VERY HELPFUL!

Thanks in advance!

Ryan

View 3 Replies View Related

Physical Memory Utilization

Feb 12, 2007

Environment: Win2003 SP1, 32 bit, SQL Server 2K5

My server has 16GB RM but it is using only 3GB. And I see my server is using 3GB of Virtual Memory, too. Why my physical memory is not being utilized? How can I increase Physical Memory usage and decrease VM usage?

Canada DBA

View 19 Replies View Related

Processor Utilization Question

Sep 20, 2007

I have a data mining app that does a series of select statements (no inserts). I'm noticing an odd occurance where if I start up 4 copies of the app on a quad core machine - sql takes full advantage of the 4 cores for a few minutes and then drops to 75% utilization - the other 25% is on the idle process. Two of the apps appear to be sharing a single proc of sql as each of their throughputs is cut by 50%. If I then start a 5th copy of the app, the machine is brought to full 100% utilization - two of the apps continue to appear to share a proc. SQL is set up to use all procs and I have even tried select the priority boost to no effect.

Any ideas how to ensure full sql utilization with the same number of apps as cores?

thanks,

View 3 Replies View Related

Capture CPU Utilization In TSQL

Jan 2, 2007

Happy New Year everyone!I would like to capture CPU Utilization % using TSQL. I know this canbe done using PerfMon but I would like to run TSQL command (maybe onceevery 5 minutes) and see what is the CPU Utilization at that instant sothat I can insert the value in a table and run reports based on thedata.I have spent a good amount of time scouring google groups but this isall I have found:SELECT(CAST(@@CPU_BUSY AS float)* @@TIMETICKS/ 10000.00/ CAST(DATEDIFF (s, SP2.Login_Time, GETDATE()) AS float)) ASCPUBusyPctFROMmaster..SysProcesses AS SP2WHERESP2.Cmd = 'LAZY WRITER'Problem is this gives me total amount of time CPU in %) has been busysince the server last started. What I want is the % for the instant -the same number we see in Task Manager and PerfMon.Any help would be appreciated.Thanks

View 3 Replies View Related

Column Space Utilization

Aug 3, 2007

Hello

I created a table with column name "description" as varchar(8000). My doubt is if I am not storing 8000 characters in this column, will SQL Server use memory space needed for 8000 characters ? or will it use only the space that needs for my text. ?

Thanking You
Navaneeth

View 8 Replies View Related

SQL 2000 Server Is Always At 70-100% CPU Utilization?

Nov 27, 2007

I'm having a problem with one of my SQL servers (2000 Build 8.00.2140) where it is always reading CPU utilization of 70-100% (more often pegged at 100%).

I have an exact same SQL server running at a different location on a much less powerful (hardware wise) server that gets more traffic but only shows 7-21% CPU utilization.

Taskmgr shows Process sqlservr.exe as consuming all the resources. This is a dual 2 core 3.66Ghz (4 real CPUs) with 4GB RAM and 5 x 146 15K SCSI drives hooked up to a $1200 SCSI contoller (Dell server). RAM usage is pretty low, most I've seen is 1GB.

Is there any way to determine what specific connection/thread is causing this? Any diagnostic tools or anything that can show me specifically what is consuming this SQL server? Connection thread or anything that points back to a specific IP?

Thanks, Rob.

View 3 Replies View Related

SQL 2012 :: How To Represent Servers CPU Utilization

May 30, 2014

I have multiple instances of SQL 2012 Std Edition on a 40 physical core server.What I have done is the use the Process - SQLServr -% Processor time Stat and divided by 16 ( the max number of Cores Std ed. can use) as a instance level measure. I also use processor object stats to show how busy the server is. How to represent the servers CPU utilization?

View 1 Replies View Related

Higher CPU Utilization With SQL Server 2005

Jan 16, 2007

HI
I am facing a strange problem with SQL Server 2005 . The CPU utilization with SQL Server 2005 is higer by about 70% comapared to SQL 2000.

In the same kind of Hardware and with the DB server up , I performed the following tests
Declare @i int
Set @i = 10
While @i < 100000
Begin
Insert into arup_emp values(@i,'M',0)

Set @i = @i + 1
end

The CPU utilization average on SQL 2005 was 45% and on SQL 2K it was just 25% , I am seeing a lot of people who seems to be facing this problem but unfortunately not seeing any solution to this.

Can anyone through some light .
Please note that I have tried to also see the MAXDOP options, but get the same results.

Please help.

Thanks
Arup

View 4 Replies View Related

Query Plan Utilization Vs CPU Time...

Aug 29, 2007

I was tuning a query testing out SARG with these two queries:

select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
select col1 from table1 (nolock) where col1 like '%ABC%'

I flushed out the cache, added an index to col1, then ran those two together. Provided below are the actual query plan and stat time:

Query 1: Query cost (relative to the batch): 0%
select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
----------------------------------------------------------------------------------------------------------------------------
SELECT Index Seek
Cost:0% <------- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

Query 2: Query cost (relative to the batch): 100%
select col1 from table1 (nolock) where col1 like '%ABC%'
----------------------------------------------------------------------------------------------------------------------------
SELECT Index Scan
Cost:0% <------- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

------------------------------------------------STAT TIME-----------------------------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 7 ms.

(3 row(s) affected)
Table 'table1'. Scan count 2, logical reads 2932, physical reads 0, read-ahead reads 18, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 938 ms, elapsed time = 943 ms.

(3 row(s) affected)
Table 'table1'. Scan count 1, logical reads 2927, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 515 ms, elapsed time = 505 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

------------------------------------------------STAT TIME-----------------------------------------------------------

As expected, SARGable Query 1 did a nonclustered index seek and nonSARGable Query 2 did an index scan instead. According to the query plan, Query 1 consumed 0% relative to the batch whereas Query 2 is 100%. When I checked the CPU time, I was a bit confused because Query 1 showed CPU time of 938ms whereas Query 2 showed 515ms. I triple checked and every time I got similar results. I am sure I'm missing something, could someone please tell me what I'm missing? Thanks a bunch!

View 1 Replies View Related

High CPU Utilization With SQL Server 2005

Oct 21, 2007

Our company recently combined our DBs into one SQL 2005 Server.

Dell Power Edge 1800 with 3.00 GHz Xeon Processor 800 FSB, 1 GB of RAM
Dell Power Edge 1600 with 2.80 GHz Xeon Processor 533 FSB, 1 GB of RAM

Combined into one:
Dell Power Edge 2950 Dual Core 1.6 GHz Xeon Woodcrest Processor, 4 GB of RAM

However, the CPU utilization on this new server is maintaining at about 90% with 3.82 GB of RAM used as well. It's a Windows Server 2003 R2 x64 edition running SQL Server 2005 SP2 x64. I have searched around Microsoft's website for any information that could be of help to me, but I was unable to locate anything. I was hoping that someone could provide some insight as to why this might be occuring. Or if this is a known issue.

Thanks,
Peter

View 10 Replies View Related

High CPU Utilization On IBM Xeon Server

Oct 22, 2007

We are using an IBM Xeon server with 4 GB RAM with windows 2000 server and MS Sql server 2005.

More frequently our server responce time is very slow, although the cpu utilization is between 7 - 10 %. we can not able to run even notepad on the server. we observed that the memory occupaid by the sqlserver prog is high. If we restart the server then it will return to normal level. But we do not want restart the server frequently.

Kindly provide me suitale solution.

Srinath
DBA
Reid & Taylor
Mysore, India

View 1 Replies View Related

High CPU Utilization On Distributor And Sp_MSget_repl_commands

Apr 6, 2006

Hi,

In SQL 2005 Replication Monitor i was not seeing details for any of the publications on the "Distributor to Subscriber Histroy Tab" so i decided to stop and start synchronisation on this one publication. At this time there were approximayely 20000 undistributed commands. After the stop/start of the distribution agent, i started seeing messages like "x trasactions with x commands were delivered". Then i went and restarted all the other distribution agents using the Replication Monitor.

Has anyone experienced this kind of a behaviour?

The second issue is that our trasnactional replication looked to have caught up but i was supprised find that the distribution server was running at 100%. A profiler trace of the distribution database revealed that sp_MSget_repl_commands procedure was being executed and costing approximately in excess of 400 000 reads, 7000 in CPU cost and 15sec in duration. To me it looked as if sp_MSget_repl_commands has chosen an inefficient execution plan but then realised i couldn't recompile system procedures. I think a stop and start of the SQL instance is the only option i have.

PK

View 1 Replies View Related