Perf Tuning Question - Gurus Only
Feb 19, 2004
Howdy
I have a server that has the following average readings :
No. CPUs = 1
% CPU = 2
SystemCPU Queue Length = 2 to 4
SQL Server:Buffer ManagerBuffer Cache = 99.85%
RAM in the box = 1 GB
MemoryPages/sec = 1 to 5
SQL memory in use ( using Task Manager ) = 250 MB
Max worker threads = 255
Average number of connection = 60
So...........all indicators are that the CPU is idling, there is way enough RAM but we still have a ( in theory ) a congested CPU as the queue length is over 2 consistantly. Thing is, I need to work out if the CPU isnt working hard as the queue is long, or whether we can put extra databases/load on the box.
As the max worker threads are greater than number of connections ( 60 vs 255 ) we could reduce these as the number of users doesnt seem to alter much. BUT.......would this make much difference as if the 255-60=195 worker threads arent doing anything much, they shouldnt put any load on the server, right?
Any thoughts much appreciated.
Cheers,
SG.
View 13 Replies
ADVERTISEMENT
Feb 28, 2008
The challenge: I have to extract and convert data between 2 SQL server systems - only 4 tables on the source systems, 8 tables on the target system. Source tables have between 5,000 rows and 16,000,000 rows. For most of the tables (for example Customer, which goes into 4 target tables), there will be 1 row in target tables for each row in the mapped source system table - so my 13.5M customer rows will end up as around 40M rows across the 4 target tables. So far, so good. But - this is a 24x7 online retail web-site, and to get the data across as a clean process, we require the smallest possible duration.
I have progressed on the customer migration, and am testing on a test environment (2xdual core HT processors, 4 GB ram) which was 2.15 million rows. Live environment is likely to be a 4xdual core with 8-16 GB ram.
I am trying to optimize the extract data flow, and have read the SSISperfTuning doc. I am now trying to put that into practice.
I have a row size of approx 340 bytes, so based on that, and my test environment of 2.15 million rows, I work out at around 700 MB ram required to buffer the data. That is a factor of 7 times greater than the max buffer space for a data flow of 100 MB, which it seems, means I should divide the base MaxBufferRows (10000) by 7 to go down to 1400 rows?
I see a LOT of the following messages in my progress, when running with default settings:
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 30 buffers were considered and 30 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
The design of the data flow at the moment is:
..........................................|--target table 1
SOURCE SP ---- MULTICAST---|--target table 2
..........................................|--target table 3
..........................................|--target table 4
any thoughts on Buffer tweaking, corrections to my assumption and other hints/techniques?
*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!
View 7 Replies
View Related
Feb 25, 2008
Hi all,
I'm loading my data warehouse using several SCDs. Some of these SCDs need to occur in sequence, while others can be run at the same time. I'm wondering what the best option for me is in terms of performance. Here is what I was considering:
1) Create a single package. Create two sequence containers --- one that will contain SCD loads that occur in sequence; the other sequence container contains SCD loads that occur in parallel.
OR
2) Create a set of packages for each SCD load. Then create a "Master" package that will use "Execute Package Task" components to call these packages.
The othe reason I want to bring up these difference ways to design an DW Load is because the second option is a "cleaner" approach, or a more organizational approach, to the load. The first option can get quite messy and large if you have several SCDs and several sequence containers. However, I'm looking for the fastest performance. Any thoughts?
View 7 Replies
View Related
Jan 18, 2002
Tuning Question for Gurus!
We have a stored procedure which has the following "where" condition a,b and c are same table joined to itself.
This was taking 1 hour to run previously(which caused severe blocking on the server).Then, I modified the "where" condition as follows to improve the performance:
OLD CODE(where condition):
---------------------------
Where getdate() >= a.Start_DT
AND getdate() >= b.Start_DT
AND getdate() >= c.Start_DT
AND(getdate() <= a.end_DT OR a.end_DT IS NULL)
AND(getdate() <= b.end_DT OR b.end_DT IS NULL)
AND(getdate() <= c.end_DT OR c.end_DT IS NULL)
MODIFIED CODE(where condition):
------------------------------
Where getdate() >= a.Start_DT
AND(getdate() <= a.end_DT OR a.end_DT IS NULL)
AND getdate() >= b.Start_DT
AND(getdate() <= b.end_DT OR b.end_DT IS NULL)
AND getdate() >= c.Start_DT
AND(getdate() <= c.end_DT OR c.end_DT IS NULL)
This new code has improved the performance,and now the query is running in 1 sec!
BUT now the big question is:
------------------------
All of a sudden the old stored procedure is also started running in 1 sec! Any clue on why it happened? Also,which code is superior, performance wise??
I need to explain the reason to my management,as this has been a major problem all these days.Also,it will help them to put the right code in production.
Thanks!!
sa.
View 2 Replies
View Related
Nov 3, 2000
Probably this will be a silly question?
1. How can I Put all the Q documents from Microsoft that are related to SQL server? So that I can search fast.
2. How can I search SWYNK for all the ANSWERS that MAK/CRAIG/RAY replied to the threads. Its so weird that I answered someone and now I forgot the answer what I posted. Now I am having the same problem. I cant search.
3. I wanna put all SWYNK's Questions responses in one database say SQLSERVER, so that I can search all the threads.
I had sent many emails to SWYNK. No reply so far.
Any inputs r well appreciated!!!!!!
-MAK
View 2 Replies
View Related
Nov 22, 2000
Hi
Is there any good books for Query Tuning and Stored procedure Tuning
Thanks
View 1 Replies
View Related
Mar 15, 1999
On an installation of sql 6.5, when I go to performance monitor, there are no sql counters. What gives? How can this be fixed,and do I have to reinstall,(wipeout data) to do it?
View 2 Replies
View Related
Nov 9, 1998
The User Defined Counters Object has disappeared from me Performance Monitor.
Any ideas why or how to get it back? Thanks, Mike
View 1 Replies
View Related
Feb 24, 2004
Is there anyway that you can automate performance monitor from the cmd line. I.e feed it some parameters like server and counters and receive a log file at the end.
Cheers
View 5 Replies
View Related
Mar 19, 2003
I have a SQL2000 db running a vendor custom app on a Win2000 sp3 server. The front-end is run on Citrix to the client. Can't change the app or the somewhat poor infrastructure of client. users starting to complain about slowness ( we can shadow them and this seems the case). On the SQL box all the standard perf counters look good (buffer cache at 100%, queue length always less than 1, memory and disk look good. The server currently has 2 nics doing load balancing. The network counter bytes/sec averages over 100,000. The network counter output queue length shows an average of 4,294,967,251 (That seems high but in reading on another sql perf site this counter doesn't always work). Any other ideas where I might look. Do these numbers look high?
Thanks
View 1 Replies
View Related
Sep 22, 2005
CREATE TABLE [Perf] ([TransId] INTEGER,[FileNo] VARCHAR(80),[TimeInSeconds] INTEGER,[FileSizeMB] INTEGER,[FileName] VARCHAR(255),[StartDate] datetime)Ok!! Here's the Problem. I am working on a perf stats report. FileType is First 9 chars of the field FileName. I need to compare a similar filename from this month to last month or before ordered by file size. Maybe this is a very simple query but currrently my mind refuses to work. Seeking F1.
View 3 Replies
View Related
May 15, 2002
The SQL performance couters do not get installed with MSDE. Does anyone know how to install them??
View 2 Replies
View Related
Mar 23, 2004
Any recommendations from DBA's on good books for performance monitoring for sql 2000
View 2 Replies
View Related
Feb 26, 2008
Hello All,
I have a 64-bit SQL Server running on 64-bit OS having 12GB of RAM. The Server only hosts our Application database (its a 30 GB database). There are no major process running on it. Despite of this, the Avg Pagessec shows a count of 1500 and
the Avg Page Faultsec shows 22000. Is there any specific reason as to why this is happening? The %idle time of the disk is 87%. Also there is no major load on the server. Do let me know if you need any other input.
Thanks in Advance,
Mitesh
View 2 Replies
View Related
Jul 5, 2005
Are there really any benefit on using Read Uncommitted Isolation Level or having a NOLOCK hints for retrieve queries when the default Isolation level just Read Committed (not using COM+). I'm confused why the Community Server uses this technique perhaps for perf issues but I couldn't see any reason why...
View 1 Replies
View Related
Nov 20, 2006
I have this SP that takes several varchar columns and concatinates them all together then inserts them into a text field. I do this with a cursor which was the quickest way to get it done when it was setup...
However when I moved the process to a 2005 server (on the same physical server) the process drastically slowed down. On 2000 the process took about 7 min to handle all 350k+ rows with the processors hanging around 20-40%... On 2005 it took over 30 min (not sure how long it would take cause I killed the process) and the processors stay above 98%...
I have rewritten the process to use a while loop instead of the cursor (I wanted to do this anyways) and it had no effect. At this rate (about 1 row a second) it will take forever and this process runs everyday.
Any ideas??
Here is the procedure...
declare @srch_field varchar(8000)
declare @row int, @productid varchar(25)
DECLARE @title varchar(150), @actors_keyname varchar(1200), @directors_name varchar(400)
Declare @genres varchar(700), @theme varchar(1500), @type varchar(1500), @studio_desc varchar(100)
DECLARE @media_format varchar(50), @artist_name varchar(100), @dev_name varchar(100)
DECLARE @flags varchar(256), @starring varchar(256), @esrb varchar(100), @esrb_desc varchar(500)
DECLARE @ptrval varbinary(16), @text varchar(max)
declare @productlist table(product_id varchar(25), IDNUM int identity)
insert into @productlist (product_id)
select product_id
from music_load..globalsearch
select @row = @@rowcount
while @row > 0
begin
select @productid = product_id
from @productlist
where idnum = @row
SELECT @title = rtrim(title) ,
@actors_keyname = actors_keyname ,
@directors_name = directors_name,
@genres = genres ,
@theme = theme ,
@type = type ,
@studio_desc = studio_desc,
@media_format = media_format ,
@artist_name = artist_name,
@dev_name = dev_name,
@flags = flags ,
@starring =starring ,
@esrb = esrb ,
@esrb_desc = esrb_desc
FROM globalsearch
where product_id = @productid
Set @srch_field = isnull(@title,'')
if @actors_keyname is not null and @actors_keyname <> 'unknown'
Set @srch_field = @srch_field + ' ~ ' + rtrim(@actors_keyname)
if @directors_name is not null and @directors_name <> 'unknown'
Set @srch_field = @srch_field + ' ~ ' + rtrim(@directors_name)
if @genres is not null
Set @srch_field = @srch_field + ' ~ ' + (ltrim(rtrim(replace(@genres, 0,''))))
if @theme is not null
Set @srch_field = @srch_field + ' ~ ' + (ltrim(rtrim(replace(@theme, 0,''))))
if @type is not null
Set @srch_field = @srch_field + ' ~ ' + (ltrim(rtrim(replace(@type, 0,''))))
if @studio_desc is not null
Set @srch_field = @srch_field + ' ~ ' + rtrim(@studio_desc)
if @media_format is not null
Set @srch_field = @srch_field + ' ~ ' + rtrim(@media_format)
if @artist_name is not null
Set @srch_field = @srch_field + ' ~ ' + rtrim(@artist_name)
if @dev_name is not null
Set @srch_field = @srch_field + ' ~ ' + rtrim(@dev_name)
if @flags is not null
Set @srch_field = @srch_field + ' ~ ' + rtrim(@flags)
if @starring is not null
Set @srch_field = @srch_field + ' ~ ' + rtrim(@starring)
if @esrb is not null
Set @srch_field = @srch_field + ' ~ ' + rtrim(@esrb)
if @esrb_desc is not null
Set @srch_field = @srch_field + ' ~ ' + rtrim(@esrb_desc)
update globalsearch
set srch_field = @srch_field
where product_id = @productid
SELECT @ptrval = TEXTPTR(srch_field),
@text = credits
FROM globalsearch
where product_id = @productid
UPDATETEXT globalsearch.srch_field @ptrval NULL NULL @text
SELECT @ptrval = TEXTPTR(srch_field),
@text = track
FROM globalsearch
where product_id = @productid
UPDATETEXT globalsearch.srch_field @ptrval NULL NULL @text
set @row = @row - 1
end
View 5 Replies
View Related
Jul 12, 2006
This webcast from our partner, ETI, may be of interest to readers on the forum - we see questions quite often about high-performance interaction with other databases.
Donald
View 4 Replies
View Related
Nov 11, 2006
Trying to install 2005 Dev edition in xp pro, sp2.
Performance Monitor Counter Check Failed Error.
Had to stop the install twice at the point it verified what components were to be included as
the docs were not going to be installed. Resolved the issue.
Now it won't pass the system config test because the registry is not the way it wants it.
I look up the messages and the only solution is to hack the registry and risk my system.
This is nuts.
Isn't there some way to restart cleanly without hacking registry keys ?
Help.
View 3 Replies
View Related
Oct 16, 2006
My boss has a crazy idea that he want to implement using SQL 2005 and ASP.NET 2. Its something like this... If a user retrieve a record and someone is updating that same record a control on a asp.net page would warn that user that its being updated. Just like a stop light, green for go, yellow for warning, and red for stop. Were implementing optimistic here not pessimistic. Is this possible? I know pessimistic can lock that certain record. But what about in optimistic? Is it possible to monitor or know if a certain record or row in SQL 2005 is being access, updated, and deleted? How?
View 3 Replies
View Related
Feb 14, 2001
Hi,
How to hide my database with the users, I am deploying my application, i want to hide my database to client. Please help me regarding this.
Rajesh.
View 2 Replies
View Related
Sep 22, 2000
Our company is looking for fulltime Java/ASP/SQL gurus immediately.Big pay.If interested please send resume to polasp@hotmail.com.
Thanks!
View 1 Replies
View Related
May 14, 2002
I have 2 tables :
tab1 col1 char(10), col2 char(10), col3 char(10)....Primary key on col1+col2
tab2 col4 char(10),col5 char(10),col6 char(10)......Primary key on col4+col5
I want to insert a row from tab2 if its not there in tab1.
And I want to update col3 of tab1 from col6 of tab2 if col1+col2 matches col4+col5.
Any thoughts on doing these 2 tasks. I am sure these will be 2 differnet commads.
SQL Gurus please help....
jfk
View 4 Replies
View Related
Jul 23, 2005
Hi,I have a table in which two fields(FirstID, SecondID) together make theprimary key for the table. Let's look at the following example:FirstID SecondID******* ********2 22 32 55 55 78 79 1011 12Here is what I am trying to do. For a given FirstID (say, 2) find allSecondID with this FirstID (2,3,5). Now for these second ID's is therea FirstID other than 2? (Yes, 5). I would say FirstID=2 is related toFirstID = 5. Extending the logic FirstID = 5 is related to FirstID = 8.I am trying to write a SQL (some kind of self join, I think) which willgive me all related FirstID for a given First ID. For First ID = 2 theSQL will give me 5 and 8 in the above example. For First ID = 9 the SQLwill give me 11.Any help will be greatly appreciated. Thanks!!-Raj
View 6 Replies
View Related
Aug 30, 2005
Hi guys,we has accidently restored our Database with a back up file created months ago. we have no back up for the huge data and Stored procedures ( I know it is very stupid :( and i m sad abt it all ), guys is there any way by which i can get restore my db back to what it was before restoration.the flks in team have also deleted(shift + delete) the transaction log file ( I know it is very stupid again :( and i m very sad abt it all )..please adviceThanx in advance ashish
View 2 Replies
View Related
Oct 24, 2000
We need to return the results of a query as a single string.For example if the query returns 10 rows,all the 10 rows should be concatenated and put it in an output @returnstring. Is that possible in SQL stored procedures ??
Thanks!
View 1 Replies
View Related
May 19, 2002
SQL Server Load Balancing -- Any ideas?
There is no inherent mechanism available in SQL Server (replication, log shipping, or clustering) which allows you to load balance you database server.
Clustering is only useful for a failover situation and does not allow active/active balancing.
Is is possible to use merge replication between two identical OLTP servers
and manage transactions via MSMQ? Will this mechanism allow for a load balanced OLTP server?
Will this work? If not, why not?
What will work? Will federated servers work for an entire database??
View 1 Replies
View Related
Jul 15, 2002
I need to determine the differences between two tables with the same structure. The primary key for these tables would be a combination of all the columnhs. They use the ID field to join all their records. (i know ID is a reserved word - the client made the tables) Below is the structure of the tables.
TableA
and
TableB
----------
ID int
ComBank int
inboth int
EqorMA int
TranType int
I need to know the rows that Are different in TableA from TableB.
Any help would be appreciated.
Thanks
James
View 2 Replies
View Related
Oct 9, 2001
Hi there!
I'm trying to insert a MSWord/Bitmap/Powerpoint file into a table in SQL 2000. Unfortunately, I haven't had the luck to get it work. Here is my table structure :
Create table tbl_blob (
doc_id varchar(20) not null,
doc_blob image null )
My question is, do you have any script on how I can insert a MSWord/Bitmap file in my blob table. I will be running the script using Query Analyser.
thanks!
100NA
View 3 Replies
View Related
Jun 28, 2006
i realize i am starting to ask a lot of questions here so, thanks to all who tolerate me.
In my previous post, i figured out (with the help of r937) how to create a non-identity column using a cursor. I now have some resulting questions.
1. where do cursors live? there is no folder like triggers or stored procedures. is a cursor its own file or is it embedded in a stored procedure?
2. how do i get my cursor to "act" upon my stored procedure? I want the values in my cursor's "relativeposition" column to replace the values in my stored procedure's photo_number column. how do i do this?
BELOW IS MY STORED PROCEDURE:
Code:
ALTER PROCEDURE dbo.sp_NewPersonalPic
(
@photo_name VARCHAR(50) = NULL,
@photo_location VARCHAR(100) = NULL,
@photo_size VARCHAR(50) = NULL,
@user_name VARCHAR(50) = NULL,
@photo_caption VARCHAR(150) = NULL,
@photo_default BIT = NULL,
@photo_private BIT = NULL,
@photo_number INTEGER = NULL,
@photo_date DATETIME = NULL
)
AS
BEGIN
SELECT @photo_date = CONVERT(DATETIME,convert(char(26), getdate(), 109))
END
--SET ANSI_WARNINGS OFF
INSERT INTO PersonalPhotos
(photo_name, photo_location, photo_size, user_name, photo_caption, photo_default, photo_private, photo_number, photo_date)
VALUES (@photo_name, @photo_location, @photo_size, @user_name, @photo_caption, @photo_default, @photo_private, @photo_number, @photo_date)
--SET ANSI_WARNINGS ON
END
RETURN
AND HERE IS THE CURSOR:
Code:
DECLARE RelativePositionCursor
CURSOR LOCAL FAST_FORWARD FOR
SELECT photoId, photo_date
FROM PersonalPhotos
ORDER BY photo_date ASC
OPEN RelativePositionCursor
DECLARE @curs_id INT
DECLARE @curs_date DATETIME
DECLARE @totalRows INT
DECLARE @firstRow INT
SET @firstRow = 0
DECLARE @Results TABLE
(
curs_id INT NOT NULL PRIMARY KEY,
curs_date DATETIME,
RelativePosition INT
)
FETCH NEXT FROM RelativePositionCursor
INTO @curs_id, @curs_date
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @totalRows = (SELECT COUNT(*) FROM PersonalPhotos)
WHILE(@firstRow <= @totalRows)
BEGIN
SET @firstRow = @firstRow + 1
BREAK
END
INSERT @Results
VALUES (@curs_id, @curs_date, @firstRow)
FETCH NEXT FROM RelativePositionCursor
INTO @curs_id, @curs_date
END
CLOSE RelativePositionCursor
DEALLOCATE RelativePositionCursor
SELECT *
FROM @Results
ORDER BY curs_date
View 1 Replies
View Related
Jul 20, 2005
The Scenario:We have some data that can be in three states. One is a saved state,a temporary state, and a complete state.The complete state is the bulk of the data. This data will berequested 1% of the time, maybe even less.The saved state and temporary state will only number 10-15 records.This data will be used 99% of the time, very frequently.So to summarize there will be 10-15 records that will be used ALL thetime…. And 50,000-100,000 that will be used very infrequently.The programmer that I work with wants to combine the tables. I wantto keep the tables sepperate.My logic is that it will be much faster to work with 10-15 recordsthan sift threw 50-100k most of which are unnecessary to 99% of therequests. Also there are frequent deletions/modifications in thetemporary table(10-15 records). The complete table can NOT havemodifications or deletions (50-100k records). The optimization forthese tables is also very different.He says that since the structures are almost identical they should bein the same table, that we should just use a field to denote thedifferent types of records. He insists that my view is wrong and thatI know nothing about databases.I may be new to SQL, but am I really that out to lunch? Would it notmake sense to keep these tables separate?(Thank you for taking the time to read this)Sat
View 14 Replies
View Related
Jul 20, 2005
I am not good at SQL - hence here goesConsider this scenario of 2 tables X and Y with a many to manyrelationshipTable X (name,weightage)X1 2X2 1X3 5X4 1Table Y (name ,attrib)Y1 attrib1Y2 attrib2Y3 attrib3Y4 attrib4Relationship table Z (id,id)X1 Y1X1 Y2X2 Y1X3 Y1X3 Y2X3 Y4X4 Y4So based on Z the distribution (count of Y/ Total Y ) is likeY1 = 100*3/7 Y2=100*2/7 Y3=100*0/7 Y4=100*2/7Now I want to factor in the weighatge column in X alsoSoX1 Y1 2*1X1 Y2 2*1X2 Y1 1*1X3 Y1 5*1X3 Y2 5*1X3 Y4 5*1X4 Y4 1*1Sum: 21Given these tables ,How would I factor in the weights and get thedistribution in a single SQL query.Desired Output of SQL QueryId AttribDistrib--------------------------------Y1 attrib1 100*8/21Y2 attrib2 100*7/21Y3 attrib3 0Y4 attrib4 100*6/21ThxJP
View 19 Replies
View Related
May 2, 2007
Greetings All,
I have a SQL2005 table that I'm needing to export to a 'formatted' csv flat-file to emulate a mainframe output. I'm new to SSIS so don't beat me up to bad... The current output of my ssis csv file is as follows:
95752,95725,0001,0, ,N
11473,8704,0001,0, ,N
1279215,436612,0001,0, ,N
43595,43592,0038,0, ,N
and I need to get to here:
95752 ,95725 ,1 ,0 ," ","N ",0
11473 ,8704 ,1 ,0 ," ","N ",0
1279215 ,436612 ,1 ,0 ," ","N ",0
43595 ,43592 ,38 ,0 ," ","N ",0
Basically I need to pad the first 2 columns with trailing spaces to a fixed width of 11 characters, 3rd column convert number format and pad to fixed 5, 4th column pad to fixed 3, 5th column add double quotes around 20 character fixed. 6th column add double quotes around and pad to fixed 2. Finally adding a , 0 to each row which is used a LF/CR.
Any feedback in regards to getting pointed in the right direction would be greatly appreciated...
View 5 Replies
View Related
Feb 20, 2008
I have a problem.. My code below works fine but when I want to add user to the database, it gives me error.
I added this part
u = Membership.GetUser(User.Identity.Name)
nice = u.ToString()
and it gives error here
MyCmd.ExecuteNonQuery()
Thanks Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click
Dim name, address, nice As StringDim sConnString, sSQL As String
Dim u As MembershipUser 'Receiving values from Form, assign the values entered to variablesname = Request.Form("name")address = Request.Form("address")
u = Membership.GetUser(User.Identity.Name)
nice = u.ToString()
'comments = Request.Form("comments")'declare SQL statement that will query the database
sSQL = "INSERT into test (name, address, user) values ('" & _
name & "', '" & address & "', '" & nice & "')"
'define the connection string, specify database'driver and the location of database
sConnString = ConfigurationManager.ConnectionStrings("aspnetdbConnectionString1").ConnectionString.ToString() 'create an ADO connection object Using MyConnection As New System.Data.SqlClient.SqlConnection(sConnString)Dim MyCmd As New System.Data.SqlClient.SqlCommand(sSQL, MyConnection)
MyConnection.Open()
MyCmd.ExecuteNonQuery()End Using
Response.Write("The form information was inserted successfully.") End Sub
End Class
View 7 Replies
View Related
