DTS Performance In SQL 2005

Jun 28, 2007

Not sure if this is the right Forum for this question...

I have a DTS job running against SQL 2005. When this job executes one particular step (running a stored procedure), it takes a long time to run. If I try that same stored procedure in Query Analyzer, it runs really fast. How to fix?

(Migrating to SSIS has been considered, but we're not going there yet.)

I have tried running the DTS job with different logins, on different machines, inside DTS Designer and from a command line. Every time it is slow is DTS but fast in QA.

More Details:
This DTS job hasn't always been slow. Several months ago it was fast and then slowed down. For days and days it was slow. Then, someone installed SP2 for SQL. After that, the DTS job became lightning fast again. Then, today, it's back to the slowness.

Putting the SP through the tuning wizard only shows that we could achieve minor improvements in performance with a bunch of new indexes.

Anyone have an idea why this SP would be slow in DTS, but not in QA?

View 3 Replies


ADVERTISEMENT

Performance In SQL 2005

Feb 24, 2008

Hi,
I have installed two instances for the sql for two different application, and the task manager in Windows is showing that I am using 3.5 GB from the RAM (I have 4 GB RAM on my server).
Can sombody tell me the stebs to have monitoring on the RAM and how to have the best performance for SQL 2005.
I appreciate your help.
Thanks

View 4 Replies View Related

SQL Server 2005 Performance

Mar 10, 2008

Hello!I have a very simple structured table:id      |    datawhere "data" is a varchar(100) This table would contain a lot rows (~ 500.000.000) and I want to select all "id" where data=@data. Is it realistic that the SQL Server could serve this request on a normal webserver within 1 or 2 seconds? Thanks! 

View 1 Replies View Related

SQL 2005 Query Performance

Sep 11, 2006

Hi, I want to know if anyone have any clue about the reason why this happens.
I have a table on SQL Server 7 with 320 thousand registers and when I execute a SELECT * on it, it takes about 6 seconds to give an answer. But the same table on SQL Server 2005 Ent takes about 16 seconds, Is it normal?:shocked: :shocked:

View 3 Replies View Related

Performance Between 2005 And 2008

Apr 3, 2008

I was really impressed with the speed of sql server 2005, but when I upgraded to 2008, used the exact same database, and ran the exact same sql scripts, I noticed the performance was slower.
Is there a structural difference in 2008?

View 5 Replies View Related

SQL 2005 Performance Limits

Aug 8, 2007

Hi all,

I am migrating a data warehouse from SQL 2000 to 2005. So far, I have been able to convert all DTS's on the old server and most tables and users. I am having problems with some of my views, though. A view which involves over 5 tables, and some sub-views of those tables runs perfectly on SQL 2000, but on 2005 I get a Query Timed out Message. A typical run of this view can return from 200-1000 records. My guess is that it gets stuck somewhere in the subviews it has to run. So I wonder, what are the limitations of SQL 2005 concerning Queries and sub-queries (how many subqueries can a query have without timing out?). I mean, I would expect 2005 to have more processing capacity than SQL 2000 (on which this query runs perfectly). I have run some queries which don't run on 2000 but do run on 2005 and return over 4000 records.

Or is there some setting I haven't adjusted, like the time it takes for a query to time out? How would I adjust this, then?

Many thanks for the info.

View 3 Replies View Related

SQL Server 2005 Performance

Jun 5, 2006

I have recently upgraded from SQL Server 2000 to SQL Server 2005, and now all my queries run infinitely more slowly.

Here is the scenario - I run an extract of a MS SQL Server database at a client site, then recreate the database on our in-house server - but without indexes etc. Then I run various queries in order to created data files that will be used for importing into a global system. When I was running Server 2000, most of the queries ran in less than 10 seconds each, but under Server 2005 they take 3 minutes or more! Does anybody know of any parameters that I need to adjust to fix this problem?

View 5 Replies View Related

SQL 2005 Encryption And CPU Performance

Jan 27, 2008



Hello All,

Here is the SQL 2005 encryption environment:

1. Clustered SQL 2005 (enterprise edition) on windows 2003. HP (quad processor) with CPU affinity set to all processors.
2. Table structure where encrypted data will be stored has two varbinary (max) columns to store encrypted data. The columns are varbinary (max) b/c the data size could be more that 8K.
3. Encryption using AES (tried both 128/256) algorithm with symmetric keys.

When inserting data in the columns, CPU is staying at 50% when inserting records. Any ideas why this would be happening. Any suggestions on improving performance is appreciated..

Thanks..

View 7 Replies View Related

SQL 2000 Vs SQL 2005 Performance

Jun 22, 2007

I have sql 2000 running with a client database that is about 200 people per day. A VB front end runs it. I have some problems with performance. Would upgrading to Sql 2005 improve my database performance?

View 5 Replies View Related

What Would Be The Expected Performance Of SQL 2005

Oct 25, 2007



Greetings.

What do I now have:
A directory with Access Databases; around 20 databases, all dinamicly created;
Each Database has on average 300 tables inside, all equally structured, all created by software;
Each table has two DateTime fields, 4 double fields and 4 long int fields;
Each table has around 10000 records, average.

The Directory is shared in a Windows 2003 Enterprise server.
Around 20 users access the databases simultaneously, adding, retrieving and deleting data, over 100MBits LAN.

Here's the catch:
As fast as possible, the program needs to retrieve 1 single record matching a single date from a given table in a given database. All databases work together. It needs to gets litterally thousands of individual records in order to work properly. Per user. That means thousands of requests, but not much data in each request. That's its core job.
A small percentage of request write the record back , that is, update it. Maybe 2% of requests.

If I were to reproduce this situation in a SQL server 2005, what would be the expected time for lets say 50000 requests ?
Or should I stick to Access ?

Thanks,,
Any response will be apreciated.

Pedro Ramos

View 1 Replies View Related

MS SQL 2005: Performance – Normal CPU Vs CPU DUO/4 CORE

Mar 1, 2007

Hello,

Is performance of web application (ASP.NET + SQL Server 2005 Wrg edition + Win Server 2003 Web edition) running on server with one core duo/4 CPU generally comparable to the performance of the same application running on the same server with 2/4 physical CPU’s?

Thank you for your ideas!
Jan

View 1 Replies View Related

Microsoft SQL Server 2005 Performance

Nov 6, 2007

 Hi,I have a Microsoft SQL Server 2005 Enterprise installed on Windows Server 2003, and developing web application for 500 clients. So I am interested will I have any performance issues if I put in 'Articles' table, data for all 500 clients and then filter it on client ID, or should I make 500 'Articles' tables for every client one with different name and then change sqldatasource for gridview depending on which client is working on it. I will have, beside 'Articles' table, another 10 tables, which means 5500 tables total, if I use second approach, on first I will have only 11 tables. So I am asking is it better to have more tables with less data, or less tables with more data. And what are pros and cons for both approach. Thanks a lot! 

View 1 Replies View Related

Performance Issues With Sql Server 2005 And Adp

Jun 4, 2008

Ive got sql server 2005 WG edition running and have an access adp application which connects to it. However since upgrading to sql server 2005 from 2000 the adp project runs a lot slower. However when I install express on a machine and connect the adp project to it which sits on the same machine it runs just fine.
We have also rebuild all the indexes for the database but that doesnt fix the problem. Could someone please help...

View 3 Replies View Related

Performance Issue On SQL Server 2005

Oct 30, 2007

I have one query which is calulating running total and taking just 6 mins to run on production SQL Server 2000 server but it is taking more than 45 mins to run on QA on SQL Server 2005 server. The index and data is same on both server, What other things we can check beside the index?
Thanks

View 2 Replies View Related

SQL 2005 Performance And Update Stats

Nov 6, 2007

Recently we moved few of our databases from SQL 2000 to SQL 2005 (SP 2) using backup and restore. After the restore I did Reindex and update stats on the databases. Since then we have observed performance issues on SQL 2005 databases but this performance problem vanishes the moment we run (sp_updatestats). Is this a problem with SQL 2005 that we have to run sp_updatestats 2 times a days or 3 times a day. In SQL 2000 we ran it only Once a week and still we never had any performance issues. Is there any config change we need to do to fix this problem in SQL 2005?


Thanks !

View 11 Replies View Related

SQL Server 2005 Varchar And Performance

Sep 4, 2007

Does using varchar in SQL Server 2005 significantly affect performance on updates?

Why or why not?

I have seen many SQL Server databases with many varchar columns - in other databases other than SQL Server it is advised not to use varchar because it significantly impacts performance.

I am trying to weigh when to waste space to help performance.

View 3 Replies View Related

Query Performance - SQL Server 2005

Oct 17, 2007

Hi,


I am having a table with 40 columns and it contains 4 million records. I got the results for one year in 40 secs. After tuning, it is retuning in 24 secs( what i have done is i created index on order by fields).


Can you please suggest me in which way I can increase the performance.

Note: I am using only one table with Primary Key.

Thanks
Dinesh

View 7 Replies View Related

SQL Server 2005 Performance Issue

Sep 7, 2007

Hi

I'm not sure I chose the right forum, so any comments on that are also welcome

We recently changed from SQLserver2000 to SQLserver 2005 in the beginnen all went fine.
But now we are struggling with a severe performance problem...
suddenly SQLserver2005 reaches its max and is not longer able to work properly -> Extremely slow

I'm wondering if there are other people / companies / ... sharing this same issue?

Thanks for time and effort



Kind regards
Wimpos

View 2 Replies View Related

SQL 2005 And Encryption Performance Overhead

Jul 9, 2007

Does anyone have any specific performance information using AES or other encryption schemes supported by SQL Server 2005?

- What method did you find works best?

- Did you encrypt any XML Data?

- Did you encrypt specific columns or whole tables?



Thanks,

Michael

View 1 Replies View Related

UDF Performance Drop From 2000 To 2005

Oct 18, 2007

Hi all,

We are in the process of upgrading a sql 2000 database over to 2005 and have noticed some substancial performance drops with scalar udfs in 2005.

I have already read the following post
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=491984&SiteID=1
and recognise that udfs are not the most performant option in the first place, but was surprised how much slower these have become on 2005.

Has anyone else had this sort of issue, we really don't want to go away from the udf's but would like to know if there is a design issue within a udf that might be causing this (or even a usage issue). What I am getting as is: Is there certain types of queries, or keywords that should be avoided in udfs on 2005?

A simple example we have is a udf that returns an exchange rate stored in the db, this has parameters of "from currency", "to currency", and date.



SET @ret = ( SELECT TOP 1 TELE_TRANSFER_RATE

FROM dbo.EXCHANGE_RATES

WHERE EXCH_CURRENCY_NO = @from

AND CURRENCY_NO = @to

AND RATE_DATE <= @date

ORDER BY RATE_DATE DESC )

RETURN @ret


And then this is called from a script that returns financials, standard select statement, with udf call in select clause.

Any comments?

Thanks in advance.
Clint Colefax

View 6 Replies View Related

Performance When Migrating From 2000 To 2005

Feb 10, 2008

Hi guys
We are in the process of moving from SQL Server 2000 to 2005. In this process in general I have noticed that performance is better as a result of the move but in a couple of specific cases performance is about 10 time worse as a result of the move and i am wondering if anyone can tell me why.

1) Should I be noticing that calling functions from within a where clause are slower in 2005.

2) Has the and/or logic processing been changed between the different versions.

3) Why does this segment of code run really slow in 2005 but really fast in 2000 (note, i know that its not nice looking but it is pre-existing code from before we came on board and there are more examples of these so its a bit of a change to go through and fix it all up to what it should be but i need to know why before i can move on and as i said i know its not nice and one should expect it to be slow but i specially need to know why it would run fine in 2000 and not on 2005):

.....
AND (Deleted = 0)
AND (DATEDIFF(d, dbo.GetStartOfDate(ReviewedDate), dbo.GetStartOfDate(GETDATE())) = 3)
OR (ProgressPointId = 32)
AND (Deleted = 0)
AND (DATEDIFF(d, dbo.GetStartOfDate(ReviewedDate), dbo.GetStartOfDate(GETDATE())) = 3)
OR (ProgressPointId = 30)
AND (Deleted = 0)
AND (DATEDIFF(d, dbo.GetStartOfDate(ReviewedDate), dbo.GetStartOfDate(GETDATE())) = 3)
....

Thanks for your help.
Anthony

View 5 Replies View Related

GUI Performance Reporting Services 2005

Jan 16, 2007

Having to set up a large number of subscriptions for first time users and moving around the application is painfully slow, specifically when opening or saving subscriptions.

Even very slow opening reports, at times takes longer for RS to submit the query than it does for the cubes to return the results.

Anyone have any idea of any performance tweaks, or an indication of what I should be looking at.

View 1 Replies View Related

Poor Performance On Sql 2005 Vs. Sql 2000 - AGAIN!

May 15, 2008

I was hoping I wouldn't be another poster with performance issues after migrating to SQl 2005 from SQL 2000 but here I am.

I am in the process of testing out our databases on Sql Server 2005 for migration from SQL Server 2000 and there are certain portions of code that have been affected negatively. I have read thru many of the posts here and have tried out most of the recommendations. I will start out with things I've done and then provide the actual SQL.

1) I have rebuilt all indexes ( using the DBCC REINDEX using the table option).
2) Updated the db engine to latest hot fix (build 3239) that addresses speed related fixes.
3) I also ran sp_createstats using the 'fullscan' option to create stats on all columns of all tables (minus indexed columns)
4) Since nothing seemed to work, I even ran UPDATE STATICS with FULL SCAN on all tables even though I did not need it as the REBUILD woudl have created stats. But I was willing to try anything.

I have confirmed that the execution plans are different even though the data on both sql 2000 and sql 2005 are identical (i put a copy on 2005). The plans themselves are huge as the queries are huge. Here is the query.


SELECT InterimView.* ,TestView.*

FROM View_LabDataExport_TestFormData_55 TestView
RIGHT OUTER JOIN ( SELECT ReqView.*, CDView.*
FROM View_LabDataExport_FormData_55 ReqView
LEFT OUTER JOIN View_LabDataExport_FormData_CD_55 CDView
ON ( CDView.DB_SubjectID_CD = ReqView.DB_SUbjectID )

) InterimView

ON ( InterimView.DB_FormID = TestView.DB_FormID_T AND

InterimView.DB_LabSampleID = TestView.DB_LabSampleID_T )

The above query takes abotu 8 secs to run on 2000 and about 1 minute to run on 2005. This is for a small dataset and on larger datasets this is only going to more pronounced ( as confirmed by other teams that have already migrated in my company). Another point worth mentioning might be if I remove the TestView.* from the select list, it works in 5 to 6 seconds. Is there an issue with Sql 2005 and a large number of columns or anything of that sort? On 2000, the time remains the same , about 8 seconds if I remove this from the select list.

Here is the statistics ion on 2005


(21234 row(s) affected)

Table 'Worktable'. Scan count 75490, logical reads 3676867, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabTestToReportPanel'. Scan count 476, logical reads 1524, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabReportPanel'. Scan count 0, logical reads 260, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'DiscreteValue'. Scan count 1, logical reads 176106, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabReleasedSampleTest'. Scan count 1, logical reads 2078, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabSample'. Scan count 1360, logical reads 18567, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Form'. Scan count 2302, logical reads 8225, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabTest'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabSampleDef'. Scan count 1, logical reads 10530, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabArea'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Lab'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Location'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Study'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Item'. Scan count 1335, logical reads 32940, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ObjectState'. Scan count 1, logical reads 10972, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Object'. Scan count 0, logical reads 20674, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Subject'. Scan count 0, logical reads 3293, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'FormDef'. Scan count 2, logical reads 70, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'PrintedLabSampleLabel'. Scan count 0, logical reads 13144, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'PrintedForm'. Scan count 0, logical reads 4219, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'StudySite'. Scan count 0, logical reads 2756, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'StudyEvent'. Scan count 18, logical reads 40, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'StudyEventDef'. Scan count 0, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'FormDefToStudyEventDef'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabSampleDefToFormDef'. Scan count 1, logical reads 255, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Here is the statistics ion on 2000

Table 'LabTestToReportPanel'. Scan count 2123, logical reads 4820, physical reads 44, read-ahead reads 0.

Table 'LabReportPanel'. Scan count 130, logical reads 260, physical reads 0, read-ahead reads 0.

Table 'DiscreteValue'. Scan count 103914, logical reads 208214, physical reads 0, read-ahead reads 0.

Table 'Location'. Scan count 19031, logical reads 38062, physical reads 2, read-ahead reads 0.

Table 'Lab'. Scan count 19031, logical reads 38062, physical reads 0, read-ahead reads 0.

Table 'LabArea'. Scan count 19031, logical reads 38062, physical reads 0, read-ahead reads 0.

Table 'LabSampleDef'. Scan count 24670, logical reads 49340, physical reads 0, read-ahead reads 0.

Table 'LabTest'. Scan count 19406, logical reads 39575, physical reads 0, read-ahead reads 0.

Table 'LabReleasedSampleTest'. Scan count 4289, logical reads 73865, physical reads 1014, read-ahead reads 24.

Table 'Study'. Scan count 4291, logical reads 8582, physical reads 0, read-ahead reads 0.

Table 'LabSample'. Scan count 5647, logical reads 31382, physical reads 308, read-ahead reads 4.

Table 'Form'. Scan count 4291, logical reads 9272, physical reads 2, read-ahead reads 10.

Table 'PrintedLabSampleLabel'. Scan count 4289, logical reads 17097, physical reads 114, read-ahead reads 308.

Table 'ObjectState'. Scan count 6860, logical reads 13760, physical reads 1, read-ahead reads 0.

Table 'Object'. Scan count 6860, logical reads 23559, physical reads 90, read-ahead reads 701.

Table 'PrintedForm'. Scan count 1375, logical reads 4505, physical reads 40, read-ahead reads 16.

Table 'StudySite'. Scan count 1378, logical reads 2756, physical reads 4, read-ahead reads 0.

Table 'Subject'. Scan count 1599, logical reads 3332, physical reads 2, read-ahead reads 0.

Table 'StudyEvent'. Scan count 18, logical reads 52, physical reads 0, read-ahead reads 0.

Table 'StudyEventDef'. Scan count 18, logical reads 54, physical reads 0, read-ahead reads 2.

Table 'FormDefToStudyEventDef'. Scan count 1, logical reads 69, physical reads 0, read-ahead reads 23.

Table 'FormDef'. Scan count 2, logical reads 78, physical reads 1, read-ahead reads 4.

Table 'LabSampleDefToFormDef'. Scan count 1, logical reads 308, physical reads 1, read-ahead reads 306.

Table 'Item'. Scan count 1335, logical reads 36510, physical reads 140, read-ahead reads 1047.

(21234 row(s) affected)

(147 row(s) affected)


One difference between the two is the work table that 2005 creates versus 2000. I can attach the plans but they are huge. I will attach it if you ask.

What I was looking for was suggestions on what I could do short of rewriting code or any suggestions in general.

Thanks

View 20 Replies View Related

SQL 2005 Server Performance And Maximum Memory Pro

Aug 13, 2007

A query was taking 20 seconds and consuming 70% CPU takes only 1 second after setting Maximum Memory property to 2048 MB - why?

Server:
OS Microsoft(R) Windows(R) Server 2003, Enterprise Edition
Version5.2.3790 Service Pack 1 Build 3790
8 GB memory
Two Dual-core AMD Opteron 285 2.6GHz Processors
Server is not configured for AWE
Fiber channel connection to EMC Clarion - two LUNs - one for MDF, one for LDF

SQL 2005
SQL 2005 32 bit Standard Edition - SP1 (version 9.0.2047)
Three instances installed on server - only one instance in use
Binaries and system databases on local mirrored disk
Database file (MDF) on one EMC LUN - dedicated physical drives
Log file (LDF) on one EMC LUN - dedicated physical drives

Query in question:

SELECT TOP 10 Address.Address1, Address.Address2, Address.City, Address.County, Address.State, Address.ZIPCode, Address.Country, Client.Name,
Quote.Deleted, Client.PrimaryContact, Client.DBA, Client.Type, Quote.Status, Quote.LOB, Client.ClientID, Quote.QuoteID, Quote.PolicyNumber,
Quote.EffectiveDate, Quote.ExpirationDate, Quote.Description, Quote.Description2, Quote.DateModified, Quote.DateAccessed, Quote.CurrentPremium,
Quote.TransactionDate, Quote.CreationDate, Quote.Producer FROM ((Client INNER JOIN Address ON Client.ClientID = Address.ClientID) INNER JOIN Quote ON
Client.ClientID = Quote.ClientID) WHERE (Quote.Deleted = 0) AND ((Address.AddressType)='Mailing') ORDER BY Client.Name


Address table - 161,075 rows
Client table - 161,634 rows
Quote table - 59,145 rows


With default maximum memory setting (2,147,483,647 MB) - query runs in 20 seconds and consumes over 70 % of the CPU.

After changing maximum memory setting to 2048 MB, query runs in less than 1 second.


Question is:
What is the best practice for setting the minimum and maximum memory settings for SQL 2005?
What can be monitored to identify the cause of these type of issues - using profiler, PerfMon, other tool?

Thanks

View 2 Replies View Related

Sql Server 2005 Performance Tuning Book

Aug 26, 2007

Hello everyone ,

I am looking for an useful sql server 2005 performance tuning book. i have been searching for a real nice book as i m going to start my job from next month in a financial domain with one of the requirement as sql server 2005 performance tuning.so i m looking forward a book which can help me doing well at my workplace. Any suggestions and links appreciated in advance .

View 1 Replies View Related

Partition Merge Performance - Sql Server 2005

Mar 11, 2007

Does anyone know of any documentation on the performance of partitionmerge/split? Does the merge or split of a partition cause any lockingon the partitioned table? If you were merging or splitting a largevolume of data rebalancing your partitioned table would youpotentially lock users out?

View 2 Replies View Related

How To Repair Performance Counters For SQL Server 2005 ?

Sep 11, 2007

My Performance Counters for SQL Server 2005 are corrupted. How do I repair them ?

Any help would be appreciated. Thanks.

Salyx

Specs
Windows 2003 Standard, AMD x64.
SQL Server 2005; x64; 9.00.3042.00; SP2 Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service
Pack 2)
This is a new install, so no "upgrade from SQL 2000".
This is a production server, so "reboot" is hopefully not part of the suggested repair.

Symptom
Open Performance Monitor. Open Add Counters. Open Dropdown "Performance Object". Instead of the SQL Server Performance Counter names, a list of 4-digit numbers appears. Other Performance Counters, eg, Processor, work as normal.

Attempted repair 1 - Recovery of system performance counters
Open Command Prompt
CD WindowsSystem32
lodctr /R
This failed to restore the full set of performance counters for an unknown reason.

Attempted repair 2 - Recovery from a backup file from a second host
I used the performance counter backup file from a second host which has an identical windows install. This properly restored the system performance counters, but failed to restore the SQL Server ones. This seems odd, because both system have - as much as I can tell - the same applications installed.

Open Command Prompt
CD WindowsSystem32
REM Load backup file from second host
lodctr /R:c:PerfStringBackup.INI

Attempted repair 3 - Recover SQL Server - specific counters
Open Command Prompt
CD WindowsSystem32
REM Load backup file from second host
lodctr /R:c:PerfStringBackup.INI
REM Clear and re-load MSSQLServer counters...
unlodctr MSSQLServer
lodctr "/R:C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnsqlctr.ini"

Executing this pretty much wiped most performance counters. Only a small sub-set is now available.

More Info
SQL Server 2005 and later SP2 were installed under the administrator account.
MSSQLServer service runs under its own Windows Account (permission issues ??)
I get Event Log entries regarding x86 vs x64 Performance Counter Libraries. These, however, do not refer to ASP, not SQL Server.
I have 2 (virtually) identical hosts (same install sequence of apps). The Performance Counters on the second host work fine.
Exctrlst.exe lists MSSQLSERVER service, but I don't know how to diagnose the details.

View 3 Replies View Related

Jdbc Performance Issuses With 2005 Vs 2000

Aug 18, 2006

I cannot get performance out of sql server 2005 through jdbc connections.
I have used multiple drivers against 2000 and 2005. 2000 always comes out on top.
I installed the os the same, configured the raid array the same, configured the os the same, configured the database the same, installed the software the same, etc.

It seems to come down to the jdbc driver and the way the database handles batch requests from jdbc. Is there some configuration in 2005 that I need to alter to improve the performance of batched inserts through the jdbc driver?

View 5 Replies View Related

How Do U Improve Performance Of Database In MS SQL 2000 Or MS SQL 2005?

Mar 11, 2008

What are the ways to do that ?

View 2 Replies View Related

Performance To Write Data In SQL Server 2005

May 25, 2007

Dear friends,

A simple question to you... witch of this dataflow destination is better for performance to write data in a SQL Server 2005:



1. SQL Server Destination?

2. OLEDB Destination?

3. Other?



Thanks!

View 1 Replies View Related

Compare 2000 With 2005 Performance - 32/64 Decide To Buy?

Aug 14, 2007

I need to show my boss that 2005 will on average be faster than 2000.

Are there any performance benchmark results available to show this?

Also I need similar benchmarks to show 64 bit will be faster than 32 bit SQL 2005.

Ian

View 6 Replies View Related

SQL Server 2005 SP2 (X64 Version) Performance Issues

Apr 12, 2007

Hi,



We have our SQL databases clustered using MSCS on X64 servers and are planning to apply SP2. During initial tests, we find that around 20-25% queries perform slow after applying SP2 compared to SP1. Just wanted to know if anyone else has found the same behavior and if there are any known patterns / issues with respect to performance for Sp2

View 2 Replies View Related

MSDE Performance Verses SQL 2005 Express

Aug 24, 2006

I have been testing one of our Apps under SQL 2005 Express and I am seeing a big downgrade in performance compared to MSDE on the same hardware. Has anyone else experienced this?

Cheers

Aussie Coder

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved