SS2000 Performance - Unconvincing
Mar 3, 2003
Dear Listers,
Has anyone experienced performance degradation after converting / uprading from SS7 -> SS2000 ? .... wrt executing a reporting stored proc. that creates dynamic sql and makes extensive use of temporary tables.
If so, what measures were taken to progress towards a final resolution ?
Our scenario:
SS7 code was executed in isolation. ie. it was the only instance running on the box. When the SS7 job had completed, the SS7 instance was shutdown and the SS2000 instance was started .... then the SS2000 code was executed
- Identical h/ware (The SS7 and SS2000 were installed on the same box)
- Identical schema, indexes, triggers, views
- db files on identical RAID5 array
- Stats updated
- Tables reindexed
- data & procedure caches purged
Note that the temporary table(s) were NOT indexed.
TIA
John
View 1 Replies
ADVERTISEMENT
May 6, 2004
Hi all,
I have a simple query which returns all the names of products attached to a particular order.
select a.orderid, b.product_name
from orders a, order_items b
where a.orderid = b.orderidSay it returns this data: (sorry about the formatting!)
orderid product_name
===== =========
001234 Sweater (Black)
001234 Trousers (Large)
001234 T-Shirt (Pink)
What I want to do is, instead of getting 3 rows back, I want to roll up all matching values (of product_name) from the order_items table into a simple string seperated by a comma. So, for the data above I would get a single row with the orderid and a string containing something like this: "Sweater (Black), Trousers (Large), T-Shirt (pink)".
I'm sure there's an easy way to do this in SQL Server 2000 but I've not been able to work out how to do this and I couldn't see anytihng in SQL Books Online..
TIA for any help...
Mike
View 8 Replies
View Related
Nov 30, 2007
Today an instance periodically becomes unable to accept connections. The service will respond to a service-shutdown and when restarted will accept connections for a minute or two but then refuses to accept connections or service active connections. We have gone through several cycles of this.
The log contains rows of :
2007-12-03 13:16:33.46 spid2 Process ID 55:0 owns resources that are blocking processes on Scheduler 7.
2007-12-03 13:16:38.47 spid2 Process ID 55:0 owns resources that are blocking processes on Scheduler 7.
2007-12-03 13:16:43.49 spid2 Process ID 55:0 owns resources that are blocking processes on Scheduler 7.
Any comments??
Michael (the perplexed)
View 1 Replies
View Related
Apr 30, 2007
I have MS SQL Server 2005 Developer Ed. and try to make a database diagram of remote MS SQL Server2000 database in MS SQL Server Management Studio.
I receive an error "Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects."
View 1 Replies
View Related
Sep 6, 2007
We have several servers, each with many jobs. Some jobs run as Owner "SA", and some jobs running as various domain admin accounts. We have been told to modify all jobs to run with Owner "SA". Now, some of the jobs read or write flat files and I am a little concerned about this. Does the domain account which starts the SS2000 services determine the rights to read and write files? If so, I guess changing all jobs owners to "SA" has no real risk?
Thanks!
Michael
View 1 Replies
View Related
Nov 12, 2006
Requirement: Some data (basically a SELECT) FROM SQLServer(2K) toOracle/Lnux on a CONTINUAL basis.Does NOT need to be Real-Time...can be like (Oracle's) Materialized View,i.e. automagically refreshable.What are my options in impleneting this process ?thanks--10gR2/Linux
View 1 Replies
View Related
Mar 11, 2008
Hello,
Is it possbile to migrate SS2000 DTS packages to SS2005 SSIS involving databases with compatibility mode set to 80. If yes, what are the potential issues, which may occur?
Sajish
View 6 Replies
View Related
Sep 27, 2007
we have a 2000 db that we'd like processed by RS2005. We dont want to convert the db to 2005. Is this possible?
View 1 Replies
View Related
Sep 17, 2007
I have to force a space allocation to a DB file in order to stop a Diagnostic Manager alert which states that the database is over 80% full. Now, I don't think this alert is rational, nor do I think it is useful in any way but that's the way it goes. My only option is to allocate more space to the DB so that the space used will fall below 80% so the alert will stop being issued.
So, can you tell me how to force a space allocation on an existing db file?
Thanks,
Michael
View 1 Replies
View Related
Nov 1, 2007
Hi,
We are implementing push transaction replication from Production SQL Server 2000 database to SQL Server 2005 reporting server and SQL Server 2000 Test server.
SQL Server 2000 PROD to 2000 Test is simple but from SS2000 to SS2005 I have following questions:
Is it feasible to create push subscription from SQL Server 2000 to SQL Server 2005 database?
When I am trying to setup publisher/subscription properties, It only give me options to pick SQL Server 7.0 or 2000 or heterogeneous databases.
When I am trying to register SQL Server 2005 database in EM of SS2000 it don't allow me to add it and through exception that I need to use Management studio, Is there any alternate way to register?
Thanks
--rubs
View 1 Replies
View Related
Jan 15, 2007
My co-worker, no idiot, says that the tooltip in the system tray forthe SS2000 icon usually shows the computer and server, but once in awhile shows the password (two slashes and the password).Is this a known bug of some sort? It really shouldn't be showing hispassword. It's likely the developer edition, but still, that isn'tgood.
View 2 Replies
View Related
Feb 15, 2008
In SS 2000 I want to create a sproc that returns the correct address block for a contact. I want to concatenate AddressLine1 and AddressLine2 if AddressLine2 has a value. When I run the following query I get an error (below):
SELECT vwICPContacts.PersonID, vwPersons.PreferredAddress, vwPersons.Email1,vwPersons.email2, vwPersons.email3,
CASE
WHEN vwPersons.preferredAddress='Home Address' then
case
when (isnull([vwpersons].[HomeAddressLine2],'') = '') then [vwPersons].[HomeAddressLine1]
when (isnull([vwpersons].[HomeAddressLine2],'') <> '') then [vwPersons].[HomeAddressLine1] + Char(13) & Char(10) & [vwPersons].[HomeAddressLine2]
END
ELSE
case
when (isnull([vwpersons].[AddressLine2],'') = '') then [vwPersons].[AddressLine1]
when (isnull([vwpersons].[AddressLine2],'') <> '') then [vwPersons].[AddressLine1] + Char(13) & Char(10) & [vwPersons].[AddressLine2]
END
END
AS MailingAddress
FROM (vwPersons INNER JOIN vwICPContacts ON vwPersons.ID = vwICPContacts.PersonID) LEFT JOIN vwCompanies ON vwPersons.CompanyID = vwCompanies.ID
Error Message:
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals boolean AND, type equals nvarchar.
I've done this before but am completely stumped. Any ideas?
Jane
View 4 Replies
View Related
Oct 25, 2007
Hello all;
I am trying to form a replication system but at the very beginning i couldn't pass an obstacle.
While trying to create the Replication it says i have to change the user which starts the SQL Agent because the current starter user account is a system account and this will make the replication between servers fail.
"SQL Server Agent on OZN currently uses the system account, which causes the replication between the servers fail. In the following dialog box, specify another account for the service startup account."
I change it in the properties dialog box of the SQL Server Agent. The new account is the one I formed and granted accordingly. But it gives the following error when I try to apply the changes.
" Error 22042: xp_SetSQLSecurity() returned error -2147023564, 'No mapping between account names and security IDs was done' "
I tried many things, searched in the net, changed the owner of the database, applied new accounts, many grants, applied service pack 4, etc...
If anyone helps it will be very much appreciated. Thanks in advance...
View 3 Replies
View Related
Jul 19, 2007
I have no idea where to post this kind of question, so here it is!
I have a requirement to retrieve oracle 10 data into SS2000 in as near real-time as possible (stupid users!) and join with resident SS data for on-demand reporting. (We use SS replication to populate some reporting tables from other SS2000 instances and this has spoiled the users as well as the developers! )
I would like to know if there are any clever ways of doing this, or if a plain-old DTS package running in some kind of loop is the practical answer. 1 minute delay is probably too long . . . I don't know if data can be pushed from the oracle side. Or if we need to write a Service and use it to suck and push.
Any suggestions?
thanks!
View 1 Replies
View Related
Sep 12, 2004
1. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection
above, which way would be faster and get a better performance?
View 2 Replies
View Related
Jun 23, 2006
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
Jun 22, 2006
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server witha particular query. It would take approximately 22 seconds to return100 rows, thats about 0.22 seconds per row. Note: I ran the query insingle user mode. So I tested the query on the Development server bytaking a backup (.dmp) of the database and moving it onto the devserver. I ran the same query and found that it ran in less than asecond.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue isrelated to some external hardware issue like: disk space, memory etc.Or could it be OS software related issues, like service packs, SQLServer configuations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating systemrelated issue.Any Ideas would help me greatly!Thanks,Brian T
View 2 Replies
View Related
Mar 9, 2007
We have the same application installed on a few different environments with similar servers and similar hardward. The only difference is the versions of SQL and the colations.
Is SQL 2005 a lot faster that SQL 2000? Could colation type make a big effect on performance?
ScAndal
View 1 Replies
View Related
Aug 31, 2007
HiI want to insert 1000s of records into SQL Server 2005 Database with some manipulation. So that i put into the For Loop and inserting record.Inside the loop i am opening the connection and closing after use. The sample code is belowfor(int i=0;i<1000;i++){ sqlCmd.CommandText = "ProcName"; sqlCmd.Connection = sqlCon; sqlCmd.Connection.Open(): sqlCmd.ExecuteNonQuery(); sqlCmd.Connection.Close(); } What my Question is.. How is the Performance of this Code..?? Will is take time to get the Connection and Close the Connection in every itration?Or Shall I Open the Connection in Begining of the outside loop and close the connection at end of the Loop? will it increase the Performace?Please clarify me these question.. Thanks in advance.
View 1 Replies
View Related
Dec 8, 2003
I have a following problem with SQL performance:
this line 'select * from [viewUserLatestFee]' executes instantly (in Query Analiser)
this line 'select * from [viewUserLatestFee] where orgID = 1' takes up to 30 seconds for 1000 rows (still in Query analiser)
can anyone please help - I seem to have ran out of ideas
I have a feeling people might be curious about the view so here it is:
SELECT dbo.viewUserPosition.id, dbo.viewUserPosition.username, dbo.viewUserPosition.password, dbo.viewUserPosition.title,
dbo.viewUserPosition.firstName, dbo.viewUserPosition.lastName, dbo.viewUserPosition.email, dbo.viewUserPosition.address1,
dbo.viewUserPosition.address2, dbo.viewUserPosition.suburb, dbo.viewUserPosition.postcode, dbo.viewUserPosition.country,
dbo.viewUserPosition.state, dbo.viewUserPosition.mailAddress1, dbo.viewUserPosition.mailAddress2, dbo.viewUserPosition.mailSuburb,
dbo.viewUserPosition.mailPostcode, dbo.viewUserPosition.mailCountry, dbo.viewUserPosition.mailState, dbo.viewUserPosition.birthDate,
dbo.viewUserPosition.joinDate, dbo.viewUserPosition.lastUpdated, dbo.viewUserPosition.orgID, dbo.viewUserPosition.positionID,
dbo.viewLatestPaidFee.feeID, dbo.viewLatestPaidFee.mshipID, dbo.viewLatestPaidFee.name, dbo.viewLatestPaidFee.[desc],
dbo.viewLatestPaidFee.terms, dbo.viewLatestPaidFee.period, dbo.viewLatestPaidFee.periodType, dbo.viewLatestPaidFee.fee,
dbo.viewLatestPaidFee.startDate, dbo.viewLatestPaidFee.endDate, dbo.viewLatestPaidFee.deleted, dbo.viewLatestPaidFee.feePaidID,
dbo.viewLatestPaidFee.paidDate, dbo.viewLatestPaidFee.effectiveDate, dbo.viewLatestPaidFee.approved, dbo.viewLatestPaidFee.optionID,
dbo.viewLatestPaidFee.paidAmount, dbo.viewLatestPaidFee.feePaidEndDate
FROM dbo.viewUserPosition LEFT OUTER JOIN
dbo.viewLatestPaidFee ON dbo.viewUserPosition.id = dbo.viewLatestPaidFee.userID
Here is viewUserPosition:
SELECT dbo.tblUser.id, dbo.tblUser.username, dbo.tblUser.password, dbo.tblUser.title, dbo.tblUser.firstName, dbo.tblUser.lastName, dbo.tblUser.email,
dbo.tblUser.address1, dbo.tblUser.address2, dbo.tblUser.suburb, dbo.tblUser.postcode, dbo.tblUser.country, dbo.tblUser.state,
dbo.tblUser.mailAddress1, dbo.tblUser.mailAddress2, dbo.tblUser.mailSuburb, dbo.tblUser.mailPostcode, dbo.tblUser.mailCountry,
dbo.tblUser.mailState, dbo.tblUser.birthDate, dbo.tblUser.joinDate, dbo.tblUser.lastUpdated, dbo.tblRelPosition.orgID,
dbo.tblRelPosition.positionID
FROM dbo.tblUser INNER JOIN
dbo.tblRelPosition ON dbo.tblUser.id = dbo.tblRelPosition.userID
and viewLatestPaidFee:
SELECT dbo.tblMshipFee.id AS feeID, dbo.tblMshipFee.mshipID, dbo.tblMshipFee.name, dbo.tblMshipFee.[desc], dbo.tblMshipFee.terms,
dbo.tblMshipFee.period, dbo.tblMshipFee.periodType, dbo.tblMshipFee.fee, dbo.tblMshipFee.startDate, dbo.tblMshipFee.endDate,
dbo.tblMshipFee.deleted, fp.id AS feePaidID, fp.paidDate, fp.effectiveDate, fp.approved, fp.optionID, fp.paidAmount, fp.endDate AS feePaidEndDate,
fp.userID
FROM dbo.tblRelMshipFeePaid fp INNER JOIN
dbo.tblMshipFee ON dbo.tblMshipFee.id = fp.feeID AND fp.endDate =
(SELECT MAX(fp2.[endDate])
FROM [dbo].[tblRelMshipFeePaid] fp2
WHERE fp2.[userID] = fp.[userID])
View 4 Replies
View Related
Jan 13, 2005
We used a stored proc to pull totals from a database. Everything was fine until the table grew and started to time out. So we created a temp table to populate with a range of data and then pull the totals from there. Everything was fine until the table grew and started to time out. Any suggestion?
View 3 Replies
View Related
Jan 17, 2002
Hi,
I am newly joined as SQL DBA. I want to check the Physical disk Performance. we have RAID 5 with 5+1 disks. I calculated NO Of IO's Per Disk. But how do we know what is actual limit of IO's per disk.
Thanks
Praveen
View 1 Replies
View Related
May 8, 2001
What's my best bet in getting better performance out of one of my database servers? Currently we have 1 set of Raid5 disks partitioned into 2 drives. This houses everything (system, database, and logs) If that server has 2 slots left for drives I was thinking of putting 2 mirrored drives and getting the logs off the main database space? (Make sense?) This is a vendored application so working with new indexes etc. isn't something I should do wo/ the vendor's interaction. Will what I describe above help?
Thanks
View 2 Replies
View Related
Mar 31, 2001
hi,
i am using to move data from oracle to oracle.
i have used stored procedure in oracle for the update/insert .
the dts calls the stored procedure for each record, due to this the performance has gone down. how do i increase the speed of data xfer.
has any one done any thing similar ?
Tushar
View 1 Replies
View Related
Jun 26, 2001
We have SQL Server running on a dual processor Pentium 500mhz server. Our database is hit by about 300 users. 200 of those users are doing constant searches though a client table of about 250,000 records, which in turn is linked to a history table containing over 5,000,000 records. This is only the tip of the iceberg, we have many triggers, procedures, updates, etc. going in the background. The database has over 500 tables.
Keep in mind, these searches that are taking place can involve all kinds of fields: phone number, company name, fax number, first name, last name, status, wildcard searches, etc. So as you can imagine, the database is being hit with all kinds of funky requests to find records. I will be the first to admit that our developers (vendor) are not the best code writers, and we have a tough time getting them to optimize something they do not even understand themselves.
As I speak, our processor utilization is maxing out between 95 to 100 percent. I've done a lot of performance tuning and all of the problems lie in the searching. We've built, tested, rebuilt, re-tested each and every index. I even used the Profiler to filter what I could. It has improved, but our database is growing at a rate of 10 megs a day (already close to 3 gigs, not that huge). I think I've optimized my indexes as best as I can considering all the fields and possibilities available to users to search for records.
For a database that requires all of these different search criteria, what would be a more optimal server? We are looking to purchase something ASAP. I could really use help from someone in a similar situation. It seems odd, in mind, that a company of 300 people would need to rely on a quad server (four processor capability.).
Thanks. JT
View 3 Replies
View Related
May 31, 2000
HI
I have 700 to 900 mb of production database , 2 gb of ram , 30 gb hard disk,
My production machine is runnng very slow , i have check everything memory,
page/sec, catch hit ratin , dbcc dbreindex but still it performance is not up to the mark.
If i stop SQL SERVER & restart for few days machine works fine but after that
again same thing it work very slow, what could be the reason
if any one had any solution please suggest.
Thanks
Nil
View 2 Replies
View Related
Jan 17, 2000
Hi friends,
My company has aution web site, it is written in Java and all sql statements generated dynamically. No stored procedures used. If 30 users uses this site it is OK but if around 300 users uses then the site becomes very slow(almost dead) and developers saying that database is the bottle neck. Please help me in this problem how can I check and overcome this problem.
Thanks
dindu
View 2 Replies
View Related
Apr 27, 2000
I am running a SQL 7.0 server on a two processor machine. We are having some performance issues.
one of the processor is always above 90% utilization but the second is barely at 50%.
Will adding another processor help or are the processes locked to one processor.
The server is a dedicated sql server. nothing else is running on it.
Thanks for any info you can provide.
Pierre
View 2 Replies
View Related
Oct 20, 1999
Hi,
What I have to do to determine which is the capacity (transactions / sec) of MS SQL Server 7.0 on a specific hardware configuration?
Thank you,
Sebastian Bologescu
View 1 Replies
View Related
May 5, 2001
We have recently upgraded to SQL 7.0 on NT 4.0/sp6 box which has got 4 PIII 700 processors, 1GB RAM, and 70GB HDD on RAID 1 and RAID 5. We feel that the application performance is not great as expected in SS7. (The application was running in 6.5 smoothly and performance was good)
Is there any option needs to set to improve performance? Now, SS 7 using all the 4 processors and dynamically allocated memory, etc. Any thoughts greatly appreciated.
Thanks in Advance
Jaya
View 2 Replies
View Related
Mar 14, 2002
I'm running MS SQL Server on a 1.4 GHz AMD Athlon Processor with 750 MB or RAM and ample disk space. I have a table with 14 columns; 2 datetime, 8 int and the rest are varchar of various sizes less than 13.
I run a java process on another machine that connects to the database and insert records. It takes about 6 minutes to insert 100,000 records.
I run the xp performance monitor and only about 25% of the SQL Server machine's cpu is being used. I run top on the Linux box running java and I see about the same results. Neither machine is kept busy processing. Why don't I get better performance? Could my local area network be that slow? How many inserts per minutes is good performance?
Thanks for your input.
View 1 Replies
View Related
Jan 23, 2001
Does anyone know the performance differences between returning data from SQL Server as XML vs. as a record set? We are about to dive into the For XML world full force, but we wanted to make sure that we are not heading for a performance nightmare.
Thanks for any insight on this. I'll try to look for white papers and do some testing in the meantime.
View 3 Replies
View Related
Feb 5, 2004
I ave the following Code in my Stored procedure.
Declare Cursor for table A
WHILE @@FETCH_STATUS = 0
Get values from other function based on some business logic.
INSERT Into another table B
(or)
UPDATE to another table B
END
I have to insert/update values to table B, one by one row. So, it is taking more time.
Is there any way to collect the values into a temporary storage and Insert/update or Move the values to table B.
View 11 Replies
View Related
