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
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 RelatedHi 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
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
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
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?
What are the ways to do that ?
View 2 Replies View RelatedI 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.
FYI, this has also been posted on the SQL Server Engine forum.
Thanks
I have read another thread on this, but I wanted to ask a little more generally, what makes this happen?
The app I got called on is a SQL Server database with an Access front end and a web app front end. The database was on an old machine with SQL Server 2000 and it performed adequately. Then they bought a new high end machine, brought the database over in SQL 2005 and it performs worse than before.
So when I see it, I will check the usual hardware issues but I don't think there will be any.
So the question is, all else being the same, what are the usual things that cause slowness in going from 2000 to 2005?
Do you need to redo indexes? Recompile procs and views?
Any thoughts/ideas much appreciated.
Hi all,
I am wondering if anyone has similar experience.
I did the migration by detaching the database from SQL 2000 running on W2K Pro and attaching it to SQL 2005 running on XP Pro. Some queries with simple aggregate functions such as AVG() have been slowed by at least an order of magnitude. I understand that SQL server can intelligently adapt to improve the performance. I am not sure how much it can improve at this point of time.
H
Hi,
I'm having an issue with a query I'm running on Sql Server 2005. It's a semi-complex query involving an in-line table function and several left outer joins which are joined on to the results of the function call. Two of the left outer joins are then qualified in a where clause of the form where table.Col is not null; the idea is that the final result set contains data that has no match in those two tables.
The problem revolves around a where clause in the function and the last left outer join (ie, one of the ones qualified with where not null). When I alter the where clause of the function to further restrict the result set the function returns, the query times shoots up from 1 second to roughly 2-3 minutes. Note that the time the function takes to complete is not affected. The difference in time is purely down to what the query does with the results the function provides. Also note that the change to the where clause provides a subset of the original data; it does not add any more data (it actually restricts the original resultset by roughly 1000 rows).
I can bring the query speed back down again by removing the last left outer join - this join takes one of the columns from the function, and joins it to a small table - 924 rows. So it appears that this particular join is the cause of the issue, but only when using the resultset generated from the modified function query.
Now, as the thread title alludes, Sql Server 2000 and 2005 handle this differently, or appear to. When I execute this same query on a Sql 2000 machine, there's no apparent time differences, and the data that is returned is as expected. Does anyone have any suggestions as to what might be causing this and how I can fix it? I could simply return the larger resultset and use managed code to filter out the rows I don't want; however, I would like to get to the bottom of this, especially if it's going to effect future queries.
Cheers,
Chris
Hello,
We previously having two servers A and B. Server A is used for updation of data and the data then replicated to server B. Server B is used for
Server A :
purpose : used for database updation/ modification
SQL Server version : SQL Server 2000 SP 2
Server Z :
purpose : used for Reporting
SQL Server version : SQL Server 2000 SP 2
We were doing Transactional replication from Server A to Server B.
Last month we have broght another server (Server B) with same hardware configuration but having SQL SERVER 2005 installed. This is to speed up our database update process. We have moved some of the database on this new server so that we can achieve our deadlines.
Server B :
purpose : used for database updation/ modification
SQL Server version : SQL Server 2005
I have set up the transactional replication from Server B to Server Z and replication works fine.
However, the issue is after it is started replicating from this new server (Server B) performance of all the queries reduced a lot.(making my life harder)
I didnt expected this as our reporting server is still SQL server 2000.
I have restored the backup of database which was replicated from server A (sql server 2000) and compared execution plan for one of our common query (which is used in most of the reports and which is now taking longer time to provide results)
I found that database which is replicated from Server B (Sql server 2005) is having primary keys. which was not present in the database which replicated from server A(Sql server 2000).
I have then removed the primary key and make the indexes same as previous copy of database(which was replicated from server A) But still the query takes long time.
Execution plan now shows "Table Spool" which was not present in previous copy of database.
Almost every query for this database is taking longer time now.
Can someone suggest me what is wrong and what should I need to fix.
Am I going on the right direction?
Hi,
Recently we have migrated our application from MSDE 2000 to SQL Server Express 2005(SP 1). This has significantly reduced the performance of our Windows.NET application which is developed using C#.
For example : While logging in to the application two databases are being attached.
Time taken in MSDE: 16 secs
Time taken in SQL Server Express 2005 : 58 secs
Also note performance is degraded for normal screens where data is retrived from database using inline queries.
Questions:
1) Is there any special(optimum) configuration(installation parameters) while installing the SQL Server Express 2005 setup?
2) Is there any query optimization to be done w.r.t SQL Server Express 2005 ?
We highly appreciate any help towards resolving the above problem.
Regards,
Sasi
Hi,Simple question: A customer has an application using Access 2000frontend and SQL Server 2000 backend. Data connection is over ODBC.There are almost 250 concurrent users and is growing. Have theysqueezed everything out of Access? Should the move to a VB.Net frontendtaken place ages ago?CheersMike
View 4 Replies View Relatedi have two tables. 1st table gets the information from as400, then moves the data to table 2 at sql 2000 server. I need to see when as400 updateschanges or inserts new information to table 1 how to updatechange or insert into table 2?
View 4 Replies View RelatedHi all,
I have encountered a problem that I can't compare the date in SQL Server 2000.
But I know that maybe I have used "datetime" as data type for that field.
My question is that, how can I get ONLY the "date" in stead of "datetime" from the database?
Your attention is appreciated!
Thank you.
Regards,
katszeto
Hi guys,
I have a performance related question about the DTS package in sqlserver 2000 which i have developed
We have developed a DTS package which will migrate a view 'ATTRITION' from Sqlserver 2000 to an Oracle database.The design of the package is as follows
First step: It checks for the existance of the table 'ATTRITION' in oracle database, if table 'ATTRITION' is not there it will create a table called 'ATTRITION' in the oracle db.If the table 'ATTRITION' is already present in the oracle db,then the table is truncated.
Second step: The view 'ATTRITION' is migrated to Oracle table 'ATTRITION'.
For the migration, i have used a connection object which connects to sqlserver 2000 and for oracle connection i have used another connection object 'Microsoft ODBC driver for oracle' and i have joined both the connection objects with 'Transform data task' task which maps one to one from sqlserver 2000 where view 'ATTRITION' exists with oracle database where Table 'ATTRITION' exists.
Roughly i have around 65000 rows in 'ATTRITION' view of sqlserver 2000 which needs to be migrated.When im running the package on my system it takes around 4 minutes to migrate all the rows but when im running it on the server it takes a lot lot of time more than 1 hour.
The view definition im using has more than 10 tables joined together.But if its a problem of query used in the view,and if i run the view seperately it quickly displays the data hardly takes 1 minute. and even if i run the package on my local pc it doenst take much time.Now my confusion is why its taking soo much time on server.If i create a indexed view then will it solve my problem.Please suggest...
Thanks in advance
REgards
Arvind L
For example: we have 2 set {1,4,8}, {1,2,3,4,5,8,9,0}
Is there any other fast way to decide {1,4,8} is in cluded in {1,2,3,4,5,8,9,0} ?
beside loop, or insert them into two table then check not exists?
Hello,
I have a situation here...
where we are inserting data of candidate...which includes fields...
1. First Name, Middle Name, Last Name ... (varchar(50))
2. Passport ID--- (no duplicates allowed but may be null )
3. Telephone number... ( may be null but no duplicates allowed )
4. Date of Birth --- ( mandatory )
5. Email id .......(no duplicates allowed but may be null )
The problem is that we already have about 1 lakh enteries in the database..
and it is expected to grow with time...
We need to define indexers such that it doesnot take time for searching or while inserting a new datafield into the database.
I hope that i am clear.
since, i suspect that that as it grows it will become more and more slow...!
This is database to store "resumes of different candidates".
So have decided...that
1. name 2. Passport no. 3. Email id: 4. Contact number
be the fields to prevent duplicacy of candidate data...
This involves checking for candidates existence every time anybody tries to make a new entry into database.
Please help to decide over the indexers...
Any good suggestions are welcomed...
Thanks for the reply...
With regards,
Girish R. Pawar
Hi,
I have to compare the previous row with current row in a table in SQL Server 2000. Please help me how to do this in a optimized way. Table contains nearly 30 columns and rows count is more than 10 digits.
I am using SQL 2005 SSIS. I am joining several large tables and then the move result into another table in the same database.
I would like know which method is faster:
Use Execute SQL Task to insert the result set to the target table
Use the Data Flow Task to insert the result set to the target table. (Use OLE DB source to execute SQL command and then use the SQL destination)
Could you tell me why then other is slower?
Thanks.
Hi-
I have a scenario where I need to decide between SSIS and Replication. Customer has 50 GB Database in Oracle. And they want around 150 tables from Oracle database to be copied into SQL Server 2005 database. And this has to be done every night at 10.00 pm to synchronize the data.
I want to know whether the following options are possible to do and which one will give me the best performance.
Option : 1
Use SSIS to get the data (150 tables) from Oracle and import into SQL Server
Option : 2
Use Replication to get the data from Oracle and import into SQL Server. Is that possible to Replicate the 150 tables alone (not the entire Database) from Oracle to SQL?
Option : 3
Use Replication to copy the entire 50 GB Database from Oracle and store into SQL Server.
On these three options which one will give me the best performance?
Thanks in advance.
Hi all,
I have a stored proc that uses xp_cmdshell to boot off a batch file on the NT side of the box (box OS is Windows 2000 Advanced Server).
Here is the pertinent code:/*----- Kick off the NT bat job to suck over the data through the web service pipe*/
SELECT @NTCommand = 'D:TradeAnalysisWondaDataStoreJobsPullFromWONDA _InstitutionalRankings.bat ' + CONVERT(varchar(10), @ReqDate, 101)
EXECUTE @e_error = master.dbo.xp_cmdshell @NTCommand
SELECT @m_error = CASE WHEN ISNULL(@e_error, 0) <> 0 THEN (@e_error + 50000) ELSE @@Error END
IF @m_error <> 0 GOTO ErrorHandlerThe trouble is that the batch file is failing (soft error, caught internally to the batch file, which then kills itself, screaming loudly all the way).
The batch file is using the following "voice" in which to scream in pain as it dies (a.k.a., using this code to terminate itself, which kills the cmd shell and returns 13 as an error code)REM WonDBService.exe says we failed
Date /T
Time /T
EXIT /B 13
Meanwhile, back at the ranch (errr...back in stored procedure), what is being returned is a ZERO (in the first code block, @e_error is being set to ZERO when the xp_cmdshell returns from the bat file.
So, now that I have ruled out the obvious *LOL* how can I get my xp_cmdshell to realize it has failed miserably at the one, tiny, simple, not-too-much-to-ask, job that it is designed to do?
I'm trying to join a table and based on the value of a given column I would join using the column in question, however if the column is NULL then I want to make the join without the column in the join. so I think I want to do something like this:
Case E.a
when NULL
then LEFT JOIN EPD ON EPD.b = D.b
AND EPD.SD = (SELECT MAX(E1.SD) FROM E1
WHERE E1.b = EPD.b AND E1.a = EPD.a AND E1.SD <= T.WD)
Else
LEFT JOIN EPD
ON EPD.a = D.a
and EPD.b = E.b
AND EPD.SD = (SELECT MAX(E1.SD) FROM E1
WHERE E1.a = EPD.a AND E1.b = EPD.b AND
E1.SD <= T.WD)
end
however T-Sql does not seem to like my Case statement mixed into my From/join clauses.
Thanks,
Mark E. Johnson
I have a report and in it there is a dataset that of course contains a query.
I want the query conditions to be changed automatically (the 'WHERE' clause) according to the environment it runs on, so if I put the same report on different customer computers, it will act differently according to the relevant 'WHERE' clause conditions.
Is it possible to use a parameter or "solution configurations" (or something else) in order to decide the conditions in the 'WHERE' clause?
Help will be really appreciated.
Thanks in advance.
I´m wondering how to solve the following scenario with SSIS
I have a CITY table and a STATE table, I have to load a file with the information regarding to the CITY:
the state table is like this:
StateCode(PK) stateLegalCode stateName
============= ============== =========
1 01 Florida
the city table is like this:
citycode(PK) cityLegalCode cityname StateCode(FK)
============ ============= ======== =============
1 1001 Quakertown 1
the file has the following information
cityLegalCode cityName
============= ========
01-1001 Quakertown
...
how can I load the file into CITY table:
1-) with the file's cityLegalCode I have to split the string and if the two initial digits are 01 the registry must have 1 in the StateCode(FK).
how can I do something like that using SSIS???
thanks
I have a parameter for each field that lets me know if a field can be sorted or not.
What I want to be able to do is turn on or off interactive sorting for that column based on that parameters value.
In the dialog you have a check box that says enable interactive sorting. When that is clicked it appears that you get the two arrows no matter what you put in the expression.
I have tried
=IIF(allowSorting,Fields!myColumn.Value,"")
and
=IIF(allowSorting,Fields!myColumn.Value,Nothing)
but both result in the arrows still being there, just the sort doesn't work.
Is it possible to put an expression on the sort arrows appearing at all?
Does abyone know how to compare data-type xml in a temp/variable/physical table in MSSQL 2000?
I tried this works in MSSQL 2005,
Code Snippet
create Table #t1 ([c1] int identity(1,1) not null, [c2] text)
create Table #t2 ([c1] int identity(1,1) not null, [c2] text)
Insert into #t1
Values('This is a test')
Insert into #t2
Values('This is a test')
Select * from #t1
Select * from #t2
Select * from #t1 where [c2] LIKE (Select [c2] from #t2)
drop table #t1
drop table #t2
but not MSSQL 2000.
Server: Msg 279, Level 16, State 3, Line 12
The text, ntext, and image data types are invalid in this subquery or aggregate expression.
Is this true (from BOL)?
Code SnippetIn comparing these column values, if any of the columns to be compared are of type text, ntext, or image, FOR XML assumes that values are different (although they may be the same because Microsoft® SQL Server„˘ 2000 does not support comparing large objects); and elements are added to the result for each row selected.
The user wants to be able, using excel, to apply a filter to all measures in every measure group. I though that I can create a dimension with a single level with two members, let´s say "on" and "off" and depending on the selected member and using an IIF statement decide which formula applies to the calculated measures.
I have serious doubts about the performance and for this technique because I am thinking as a .Net developer and not as a cube developer. Maybe it is better to resolve it scoping the measures but I cannot figure it out.
Hi
We would like to install Sql 2005 Enterprise Edition (including database engine, reporting service, integration service and analysis service) as a sepearte instance on a server which already has Sql 2000 with reporting services and analysis services. We do not want to disturb the existing sql 2000 setup.
If we do that then what will happen to my earlier sql 2000 reporting service? Will it be upgraded to sql 2005 reporting service? I heard that reporting services are instance unaware application. Where will be the default reporting service database available?
Please help us.
Regards,
Sankar N
I have received some reports and I have been asked to decide whether these reports can be developed as an operational report or Analytical Reports.
Basically I wanted to understand what points needs to be considered while deciding whether I should go for Analytical reporting (Cubes) or Operation Reporting.
CREATE FUNCTION [dbo].[Compare]
(
@usrVal varchar(100),
@dbVal varchar(100)
)
RETURNS BIT
AS
BEGIN
DECLARE @flag BIT
DECLARE @userStrLen INT
DECLARE @dbStrlen INT
DECLARE @counter INT
--Default assignments
SET @counter=1
Set @userStrLen=len(@usrVal)
Set @dbStrlen=len(@dbVal)
IF (@userStrLen != @dbStrlen)
BEGIN
return 0
END
ELSE
BEGIN
WHILE @counter <= @userStrLen
BEGIN
IF (substring(@usrVal,@counter,1) != substring(@dbVal,@counter,1))
RETURN 0
SET @counter = @counter + 1
END
return 1
END
RETURN @flag
--SELECT dbo.Compare('sqlserver user defined functions','sqlserver is the best backend')
END
Just wondering if there are any tools available for SQL Server 2005 which allow the comparison and scripting of data and schema between two databases. This is so that I can migrate between Dev, QA, and Live easily.
A free tool would be best please..
Thanks in advance.
Gaj