SQL Server 2005 SLOW in a really fast serer

MS SQL SERVER

    Sponsored Links

    Next

  • 1. install script for the maintenance plans for the autoinstaller
    I have been looking to see if anyone has written a script to create the maintenance plans in SQL 2005. The previous commands like sp_add_maintenance_plan have all been depreciated. It appears Microsoft don't want you to script the creation. By having it scripted makes it easier to ensure the plans are set up the same on all servers and less time consuming to install. I tried the option of exporting and import however this doesn't work in so far as it doesn't appear to keep the local connection or the databases selected in the plan e.g., msdb or all user databases. It would be much quicker to be able to run this as part of the auto install. It appears to use this dll Microsoft.SqlServer.MaintenancePlanTasks.dll and other scripts I have seen runing the backend are sp_dts_getfolder sp_dts_addfolder sp_dts_deletefolder sp_dts_renamefolder sp_dts_listfolders sp_dts_getpackage sp_dts_putpackage sp_dts_deletepackage sp_dts_listpackages sp_dts_getpackageroles sp_dts_setpackageroles Does anyone have ideas on how to script out the maintenance plan creation?
  • 2. Migrate Virtual Server 2000 to Virtual Server 2005
    Is it possible to upgrade a Virtual Server SQL Server 2000 to a Virtual Server SQL Server 2005?
  • 3. SQL 2000 EE Limits
    Are there any limits to the number of rows that can exist in a table? Or records in the entire database? Or is it only limited to available resources?

SQL Server 2005 SLOW in a really fast serer

Postby Carlos » Thu, 19 Jan 2006 05:22:32 GMT

Ok I have a
1.8 MHz with 512 RAM , IDE 200 GB HDserver running windows 2003 and MSSQL 
Server  2000, when I run a query that search and analyze 1.6 million records 
it take about 11 minutes.

Now we just got a Dell Xenon 3.0 GHz with 2 gb RAM SCSI  300 GB Hard Drive 
with Windows 2003 Server and MS SQL 2005 Server, when I run same query as 
above it takes 20 minutes.

So how come my faster newer machine is slower than the old one? is the MSSQL 
2005 slower?  or Do I have to do some tweaking to the SQL 2005 ?

Thanks
Carlos 



Similar Threads:

1.Slow !! sql 2005 server in a fast server

Ok I have a
1.8 MHz with 512 RAM , IDE 200 GB HDserver running windows 2003 and MSSQL 
Server  2000, when I run a query that search and analyze 1.6 million records 
it take about 11 minutes.

Now we just got a Dell Xenon 3.0 GHz with 2 gb RAM SCSI  300 GB Hard Drive 
with Windows 2003 Server and MS SQL 2005 Server, when I run same query as 
above it takes 20 minutes.

So how come my faster newer machine is slower than the old one? is the MSSQL 
2005 slower?  or Do I have to do some tweaking to the SQL 2005 ?

Thanks
Carlos 


2.Server REALLY REALLY Slow...!!!

Hey there..

Plz...

I'm running a SQL Server v9.0.1406.
3 databases running. No replication, no automatic functions running.

A SELECT on a VIEW that returns 2569 rows, is taking up to 55 seconds.

And almost every DB requiment is taking a lot longer than it should..

Backed it up, and downloaded to my office. Restored it and there not this 
delay... Nor something like it..

I don't know not even where to start. I don't think it's a database issue... 
I'm betting more on a server issue..

Tkss for your help,
Christiano. 


3.Really really slow cursor

I'm stumped on this.

My developers proc left to run all night consumes tons of cpu but does no 
updates at all and I have to kill the connection in the morning. 

The select in the cursor declaration returns 357 rows so not an outrageous 
result set. When I run the select in Qry Analyser it runs in under 6 seconds. 
When I run the stored proc in QA it takes forever. Debug print statements 
appear up until the initial fetch and then nothing.

Can a cursor loop indefinitely?




CREATE PROCEDURE dbo.stc_Insert_Instrument AS

set nocount on

declare 
	@rc int  -- returncode
,	@errmsg varchar(250) 
,	@msg varchar(50)


select @errmsg = 'Error in proc ' + object_name(@@procid) + ': '

-- create temp tables
select 
<Snip>
into #moodystaging
	from moodystaging


select 
<Snip>
into #spstaging
	from spstaging


declare ins_cursor cursor read_only for
select
	iss.IssuerId, 
	st.SECURITY_DES,
	case isdate(st.maturity)
		when 1 then st.maturity
		else null
	end as maturity,
	case isdate(st.issue_dt)
		when 1 then st.issue_dt
		else null
	end as issue_dt,
	case isnumeric(st.cpn) 
		when 1 then st.cpn
		else null
	end as cpn,
	cp.CouponTypeId,
	st.CRNCY,
	case isnumeric(st.AMT_ISSUED)
		when 1 then st.AMT_ISSUED
		else null
	end as amt_issued,
	case isnumeric(st.AMT_OUTSTANDING)
		when 1 then st.AMT_OUTSTANDING
		else null
	end as amt_outstanding,
	r.RatingId,
	sprt.RatingId as spRatingId,
	st.id_bb_unique,
	st.id_isin,
	st.id_cusip
from 
	staging st
		left JOIN Issuer iss on st.ID_BB_COMPANY = iss.SourceId
		INNER JOIN CouponType cp on st.CPN_TYP = cp.CouponTypeName
		left join external e on st.id_bb_unique = e.externalid and 
e.externaltype='BB'
		left join #moodystaging mt on st.ID_BB_UNIQUE = mt.ID_BB_UNIQUE
		left join rating r on mt.rtg = r.rating and r.type = 'Moody'
		left join #spstaging spt on st.ID_BB_UNIQUE = spt.ID_BB_UNIQUE
		left join rating sprt on spt.rtg = sprt.rating and sprt.type = 'Sp'
where
	e.externalid is null
and	iss.sourceid is not null


open ins_cursor

declare 
	@issuerid int, 
	@instrument_name varchar(50),
	@maturitydate datetime,
	@issuedate datetime,
	@coupon float(8),
	@coupontypeid int,
	@currency varchar(3),
	@amountissued float(8),
	@amountoutstanding float(8),
	@moodyratingid int,
	@spratingid int,
	@bb_id varchar(50),
	@isin varchar(50),
	@cusip varchar(50),
	@instrumentid int

fetch next from ins_cursor into
	@issuerid,
	@instrument_name,
	@maturitydate,
	@issuedate,
	@coupon,
	@coupontypeid,
	@currency,
	@amountissued,
	@amountoutstanding,
	@moodyratingid,
	@spratingid,
	@bb_id,
	@isin,
	@cusip

print 'after first fetch'
while @@fetch_status = 0
begin
print 'processing'
	insert into instrument (
		issuerid, 
		instrumentname,
		maturitydate,
		issuedate,
		coupon,
		coupontypeid,
		currency,
		amountissued,
		amountoutstanding,
		moodyratingid,
		spratingid)
	values (
		@issuerid,
		@instrument_name,
		@maturitydate,
		@issuedate,
		@coupon,
		@coupontypeid,
		@currency,
		@amountissued,
		@amountoutstanding,
		@moodyratingid,
		@spratingid)

	-- check for errors
	select @rc = @@error
	if @rc <> 0
	begin
		select @msg = 'Insert into Instrument failed.'
		goto errhandler
	end

	set @instrumentid = @@identity

	insert into external (instrumentid, externaltype, externalid) values 
(@instrumentid, 'BB', @bb_id)

	-- check for errors
	select @rc = @@error
	if @rc <> 0
	begin
		select @msg = 'Insert into External failed for BB type.'
		goto errhandler
	end


	insert into external (instrumentid, externaltype, externalid) values 
(@instrumentid, 'ISIN', @isin)

	-- check for errors
	select @rc = @@error
	if @rc <> 0
	begin
		select @msg = 'Insert into External failed for ISIN type.'
		goto errhandler
	end

	insert into external (instrumentid, externaltype, externalid) values 
(@instrumentid, 'Cusip', @cusip)

	-- check for errors
	select @rc = @@error
	if @rc <> 0
	begin
		select @msg = 'Insert into External failed for Cusip type.'
		goto errhandler
	end

	fetch next from ins_cursor into
		@issuerid,
		@instrument_name,
		@maturitydate,
		@issuedate,
		@coupon,
		@coupontypeid,
		@currency,
		@amountissued,
		@amountoutstanding,
		@moodyratingid,
		@spratingid,
		@bb_id,
		@isin,
		@cusip
end

close ins_cursor
deallocate ins_cursor

drop table #moodystaging
drop table #spstaging

return 0 -- success

errhandler:
	raiserror ('%s %s',16,1,@errmsg,@msg)
	if @@trancount > 0
		rollback transaction
	drop table #moodystaging
	drop table #spstaging
	return @rc
GO

4.Fast in Access / Slow in SQL 2005

I am importing about 60,000 records from a text file. As each record is 
read, it is inserted into a table "TImport" via a recordset.  In MS Access 
this is completed in about 15 seconds.  We changed the database to SQL 2005 
with identical tables and now this read/insert process (60,000x) is taking 
about 6-7 minutes.

What went wrong?

The classic ASP code is listed below.

Set Conn=Server.CreateObject("ADODB.Connection")
Conn.Open strConnectionString

Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open "TImport", Conn, 3, 3


Set objFSO = CreateObject("Scripting.FileSystemObject")

 do while objFile.atendofstream <> true
			
     strRecord = objFile.readline

     f1=trim(mid(strRecord,1,10))	'date           
     f2=trim(mid(strRecord,11,6))	'time 
          ...
          ...
     f20=trim(mid(strRecord,11,6)) 'nnnn

  RS.AddNew
   RS("Date") = f1
   RS("strTime") = f2
      ...
      ...
  RS.Update

Loop



          	




--Thanks,
--Rick

5.SQL Statements with inner join too slow in 2005 runs faster in 200

I'm desperately looking for answers to following issue that we are currently 
experiencing on our test server. We inheritted one of our business app from 
another company. We replicated exact same hardware environment. The only 
difference is we are using Windows 2003 server and SQL Server 2005 with SP1 
whereas the other company who wrote this app is using Windows 2000 server and 
SQL Server 2000. We are experiencing huge performance problems running few 
types of queries. The Stored Procedure that takes 7 minutes to run, exact 
same code with same data takes about 45 minutes to complete on our end. I 
have narrowed it down to one view that is called in a trigger that inserts 
rows into a specific table. Below is the trigger code and the view.

I ran DBCC CHECKDB, DBCC DBREINDEX and UPDATE STATISTICS on all tables. 
Changed compatibility to SQL Server 2005 (90).

Its not that all queries are running slow. We did gain huge performance over 
2000 in some of the queries. Its infact 1.5times faster than 2000 except this 
particular query. I am struggling to find answers. I do really appreciate 
your input on this.

I was wondering if there is any bug in SQL Server 2005 executing such SQL 
queries with inner joins or inner join with a view in a trigger.

Thanks in advance.

====================TRigger Starts here ==========================

IF @ICNT > 0 AND @DCNT > 0 AND (
 UPDATE(SA_LSS_Mthly_Per_Loop_Rate) OR
 UPDATE(SA_LTS_Mthly_Per_Loop_Rate) OR
 UPDATE(SA_HCL_Per_Loop_Mthly_Rate) OR
 UPDATE(SA_ICLS_Per_Loop_Mthly_Rate) OR
 UPDATE(SA_IAS_Per_Loop_Mthly_Rate) OR
 UPDATE(SVA_Per_Loop_Mthly_Rate) OR
 UPDATE(SNA_Per_Loop_Mthly_Rate)) 
   BEGIN
 INSERT [_Calculation_Schedule](
  [Applies_To_Month_Dt], 
  [Calculation_Month_Dt], 
  [Study_Area_ID], 
  [Company_ID], 
  [HC_Support_Type_Cd], 
  [Reason_Cd], 
  [Updated_Field_Name],
  [Prev_Value], 
  [Curr_Value])
 SELECT
  INSERTED.Applies_To_Month_Dt, 
  apl_code.fn_Get_Process_Month_Dt(),
  NULL, cl.Company_ID, 
  'LSS', 
  'Rate', 
  'SA_LSS_Mthly_Per_Loop_Rate',
  DELETED.SA_LSS_Mthly_Per_Loop_Rate, 
  INSERTED.SA_LSS_Mthly_Per_Loop_Rate
 FROM INSERTED
 INNER JOIN DELETED
  on  DELETED.Study_Area_ID  = INSERTED.Study_Area_ID
  AND DELETED.Entered_Month_Dt  = INSERTED.Entered_Month_Dt
  AND DELETED.Applies_To_Month_Dt  = INSERTED.Applies_To_Month_Dt 
 INNER JOIN apl_code.vw_ILEC_Competitor_List cl
  ON cl.Study_Area_ID = INSERTED.Study_Area_ID 
 --only when the rate's value changes
 WHERE ISNULL(DELETED.SA_LSS_Mthly_Per_Loop_Rate,0) <>  
ISNULL(INSERTED.SA_LSS_Mthly_Per_Loop_Rate,0)
        --and it is not a housecleaning query
 AND INSERTED.SA_LSS_Mthly_Per_Loop_Rate IS NOT NULL
 --only when it is a PPA
 AND INSERTED.Applies_To_Month_Dt <> apl_code.fn_Get_Process_Month_Dt()
 
 UNION
 SELECT
  INSERTED.Applies_To_Month_Dt, 
  apl_code.fn_Get_Process_Month_Dt(),
  NULL, cl.Company_ID, 
  'LTS', 
  'Rate', 
  'SA_LTS_Mthly_Per_Loop_Rate',
  DELETED.SA_LTS_Mthly_Per_Loop_Rate, 
  INSERTED.SA_LTS_Mthly_Per_Loop_Rate
 FROM INSERTED
 INNER JOIN DELETED
  on  DELETED.Study_Area_ID  = INSERTED.Study_Area_ID
  AND DELETED.Entered_Month_Dt  = INSERTED.Entered_Month_Dt
  AND DELETED.Applies_To_Month_Dt  = INSERTED.Applies_To_Month_Dt 
 INNER JOIN apl_code.vw_ILEC_Competitor_List cl
  ON cl.Study_Area_ID = INSERTED.Study_Area_ID 
 --only when the rate's value changes
 WHERE ISNULL(DELETED.SA_LTS_Mthly_Per_Loop_Rate,0) <> 
ISNULL(INSERTED.SA_LTS_Mthly_Per_Loop_Rate,0)
        --and it is not a housecleaning query
 AND INSERTED.SA_LTS_Mthly_Per_Loop_Rate IS NOT NULL
 --only when it is a PPA
 AND INSERTED.Applies_To_Month_Dt <> apl_code.fn_Get_Process_Month_Dt()

 UNION
 SELECT.................................

================ Trigger ends here ===============================


======= vw_ILEC_Competitor_List (View) Starts here =================

SELECT DISTINCT IV.Study_Area_ID, I.Company_ID, I.Company_Nm, 
I.Legal_Entity_Nm, I.Assigned_CLEC_ID
FROM         dbo.Company AS I INNER JOIN
                     dbo.Study_Area_Segment_Company AS II INNER JOIN
                     dbo.Study_Area_Segment AS III ON II.Segment_No = 
III.Segment_No INNER JOIN
                      dbo.Study_Area AS IV ON III.Study_Area_ID = 
IV.Study_Area_ID ON I.Company_ID = II.Company_ID
WHERE     (I.ILEC_Ind = 'N') AND (I.Effective_Thru_Day_Dt IS NULL) AND 
(II.Effective_Thru_Day_Dt > GETDATE() OR
                      II.Effective_Thru_Day_Dt IS NULL) AND 
(III.Effective_Thru_Day_Dt IS NULL)

======= vw_ILEC_Competitor_List (View) Starts here =================



6. SQL Server 2000 - DELETE really slow

7. Fast SQL Server Stored Procedure runs slow in VB.NET

8. ODBC connection - faster or slower in MS Access/MS SQL Server 2000



Return to MS SQL SERVER

 

Who is online

Users browsing this forum: No registered users and 8 guest