We have MSSQL 2000 Server instance installed and working well on Windows 2003 Server machine [IBM X series-366] with 16GB RAM, 3.67GHZ cpu power, and 400GB hard disk space.
We further created an identical server instance on a new machine. More specifically, on Windows 2003 Server machine [Intel (R) Xeon (TM)] with 16GB RAM, 3.67GHZ cpu power, and 400GB hard disk space, we installed MSSQL 2000 Server and copied over all the dbs, applications ...
We were expecting same or similar performance (since processor speed, ram, hd, server and database configurations are all the same, with same indexes on same tables. However, for some reason, there is a noticeable difference in performance.
More specifically, I ran Profiler for 30 minutes on both servers simultaneously [same trace parameters]. The trace file of the new server is 3 times as large as that of the old one (i.e. It looks like more items are being processed). However, the average duration of the executed stored procedures is much longer on the new server than that of the old server.
Moreover, when I run same queries on 2 servers. The query on the new server always takes longer than that on the old server. And for tables where we don't have indexes, it takes much longer.
Following advice here(http://support.microsoft.com/kb/274750/), we configured our new server (just as was our old one configured) to use 15GB of RAM. I further compared the configurations of 2 servers by executing sp_configure (with advance options). The only difference I saw was that "remote proc trans" is set to off on the new server and on on the old server. I don't think it could affect this issue though.
Furthermore, the new server appears to have many more locks, as compared to the old server. Could it be because it is processing more items?
I cannot figure what is causing the queries to be slower on the new server.
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 ***
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
We have written an application which splits up our customers data intotheir individual databases. The structure of the databases is thesame. Is it better to create the same stored procedures in eachdatabase or have them in one central location and use the sp_executesqland execute the generated the SQL statement.Thank you.Mayur Patel
I have two SQL Server tables on the same server and in the same database. I'll call them table A and table B. They have identical schemas. I need to insert all rows in table A into table B. (Don't laugh - this is just for testing and long run the tables will reside on different servers.)
Can someone please tell me the correct task to use for this and the connection type I need for both the source and destination?
Does anyone have a good query that would return records from two tables that are found in one, but not it the other table? In my situation I have 2 tables that are duplicate tables and I need to find an additional 3000 records that were added to one of the tables. I also have a composite key so the query would have col1, col2 and col3 as the composite key. So far I have tried concatenating the 3 columns and giving the result an alias and then trying to show the ones that were not in both tables, but have been struggling. Thanks..
Hi,Currently, I am developing an application for my client. The issue is that the client has the office and warehouse which are in different locations. He wants the application run and updated all the data in to a database (called warehouse database). Every month, all records in warehouse database will be synchronized to another database which is identical with the warehouse database but it is run in his laptop at the office. Since networking is not an option, I have to create two identical database like this. Does any one know what is the good solution to this? Is there any synchronize feature in SQL Server 2005?Many thanks,
Sorry if this is a super-basic question...I'm used to join selects but not sure how to approach an append select (or something like it) I have two tables with identical field structures: a Master table with 10,000 rows and a Custom table table with 1,000 rows To keep it simple, let's say the two tables each have a FirstName field and a LastName Field. Is it possible to use a View or a Select statement (or any other method) to 'append' the rows of both tables so that the result set still has only the two columns (FirstName and LastName) and has 11,000 rows? Thanks for your help! Randy
DECLARE @SdxLastName char(4) DECLARE @TheFirstName varchar(30) DECLARE @HitCountLastNameOnly int DECLARE @HitCountFirstName int DECLARE @ThresholdLastName int DECLARE @ThresholdFirstName int
-- Set minimum row hit count for when to use the first name in addition to last name for search SET @ThresholdLastName = 2 --if we get at least this number of rows back, ignore the first name completely -- Set maximum row hit count for when to use the exact first name instead of first name soundex in addition to last name soundex for search SET @ThresholdFirstName = 12 --if we get over this number of rows back, look for exact first name match
IF @AN_CustID < 1 OR @LastName = '' RETURN --required params
-- Whenever a doctor's first name is unknown, the user will enter "DR"; therefore, do not soundex the first name in this case IF @FirstName='DR.' OR @FirstName='DR' SET @FirstName=''
-- Get the soundex of the specified physician SELECT @SdxLastName = dbo.aif_Soundex(@LastName), @TheFirstName = dbo.aif_Soundex(@FirstName)
-- Determine how many rows are returned based solely on similarity of the last name SELECT @HitCountLastNameOnly = COUNT(*) FROM PhysicianMstr p (NOLOCK) WHERE p.AN_CustID = @AN_CustID AND p.Active = 1 AND @SdxLastName = dbo.aif_Soundex(RTRIM(p.LastName))
-- Check if using just the last name soundex returns too few rows IF @HitCountLastNameOnly < @ThresholdLastName BEGIN -- Determine how many rows are returned based on last name soundex or first name soundex SELECT @HitCountFirstName = COUNT(*) FROM PhysicianMstr p (NOLOCK) WHERE p.AN_CustID = @AN_CustID AND p.Active = 1 AND ( @SdxLastName = dbo.aif_Soundex(RTRIM(p.LastName)) OR @TheFirstName = dbo.aif_Soundex(RTRIM(p.FirstName)) ) -- If too many rows, switch to exact match on first name instead of soundex of first name IF @HitCountFirstName > @ThresholdFirstName BEGIN SET @TheFirstName = @FirstName SET @HitCountLastNameOnly = -1 --negative value indicates first name exact match, not soundex END END
-- List physicians with similar sounding names SELECT p.AN_PhysicianID, pm.AN_CustID, RTRIM(p.LastName) AS LastName, RTRIM(p.FirstName) AS FirstName, RTRIM(p.StateLicNo) AS UPIN, RTRIM(mis.Specialty) AS Specialty1, pm.Specialty2, p.Active, RTRIM(pm.Degree) AS Degree, pm.Extra, p.CreatedBy, p.CreatedOn, p.ModifiedBy, p.ModifiedOn, p.PLEPhysicianMIID FROM PLEPhysicianMstrMI p (NOLOCK) INNER JOIN PhysicianMstr pm (NOLOCK) ON p.AN_PhysicianID = pm.AN_PhysicianID LEFT JOIN PLEPhysicianSpecialtyMstrMI mis (NOLOCK) ON p.PLEPhysicianSpecialtyMIID = mis.PLEPhysicianSpecialtyMIID WHERE pm.AN_CustID = @AN_CustID AND p.Active = 1 AND ( @SdxLastName = dbo.aif_Soundex(RTRIM(p.LastName)) OR @TheFirstName = CASE -- Include first name only when we don't have the minimum number of rows WHEN @HitCountLastNameOnly < 0 THEN p.FirstName --exact first name match WHEN @HitCountLastNameOnly < @ThresholdLastName THEN dbo.aif_Soundex(RTRIM(p.FirstName)) ELSE '****' --force false for first name test END ) ORDER BY p.LastName, p.FirstName
I have a robust query that returns a dataset and the data is good, however some of the records contain the exact same data with the exception of the 'Price' field. I want to combine the records that are identical and SUM the values in the 'Price' field. My query and example return dataset is below.
Query: -------- select distinct 'On-Demand' as 'Business Line', o.OrderID as 'Order #', isnull(d.DisplayCode,'UNK') as Hub, isnull(rz.RouteID,'UNK') as 'Default Route', 'On-Demand' as 'Assigned Route',
Hello all, I have a puzzle that needs solving please. I have a Stored Procedure and it works fine and produces what I want to see. I have two servers replicating and when the stored procedure is ran on the other server, it produces nothing at all but the data is verified the same. So I go to query analyzer and copy the following sql statement from the stored procedures and put in the variables it asks for and I get nothing on either server. Here is the statement:
CREATE PROCEDURE sp_Transfer ( @Weekending datetime, @Transfer datetime) AS
select jobs.jobnumber, activities.weekending, activities.material, case employees.type when '1099' then case left(lineitems.item,4) when '2392' then (lineitems.quantity * lineitems.draft1099) end else case employees.type when '1099' then case left(lineitems.item,4) when '2391' then (lineitems.quantity * lineitems.draft1099) end else case employees.type when '1099' then case left(lineitems.item,4) when '3172' then (lineitems.quantity * lineitems.draft1099) end else case employees.type when '1099' then case left(lineitems.item,5) when 'ECAD2' then (lineitems.quantity * lineitems.draft1099) else (lineitems.quantity * lineitems.labor1099) end else case left(lineitems.item,4) when '2392' then (lineitems.quantity * lineitems.draft) else case left(lineitems.item,4) when '2391' then (lineitems.quantity * lineitems.draft) else case left(lineitems.item,4) when '3172' then (lineitems.quantity * lineitems.draft) else case left(lineitems.item,5) when 'ECAD2' then (lineitems.quantity * lineitems.draft) else (lineitems.quantity * lineitems.labor) end end end end end end end end as labor, (lineitems.quantity * lineitems.vehicle) as Vehicle, (activities.meals + activities.lodging) as PerDiem, (employees.state) as FState, (states.state) as TState from jobs inner join activities on jobs.jobid = activities.jobid left outer join employees on activities.employeeid = employees.employeeid left outer join lineitems on activities.activityid = lineitems.activityid inner join states on jobs.billingstateid = states.stateid where (activities.weekending = @Weekending or activities.transferdate = @Transfer or jobs.transferdate = @Transfer) and jobs.billingstateid <> employees.stateid and lineitems.quantity <> null GO
Can anyone help tell me why this happens or has anyone ever experienced this before?
Can anyone solve this puzzle with SQL only - no temp tables and no cursors (sent to me by a friend)?
I would have done it with a cursor on SQL Server but the rules stipulate not using a cursor. Can this be done with just SQL?
You have a table:
First Name Last Name John Doe Jeff Smith etc..
Is it possible to write a query in SQL which assigns serial numbers 1; 2; 3; to the rows(in any sequence)? So the output should look like this:
Number First Name Last Name 1 John Doe 2 Jeff Smith etc..
Note that your query must work with any data for the input table. You cannot rely on the fact that there are only two rows. Number cannot be a IDENTITY column.
Hello all, I have a puzzle that needs solving please. I have a Stored Procedure and it works fine and produces what I want to see. I have two servers replicating and when the stored procedure is ran on the other server, it produces nothing at all but the data is verified the same. So I go to query analyzer and copy the following sql statement from the stored procedures and put in the variables it asks for and I get nothing on either server. Here is the statement:
CREATE PROCEDURE sp_Transfer ( @Weekending datetime, @Transfer datetime) AS
select jobs.jobnumber, activities.weekending, activities.material, case employees.type when '1099' then case left(lineitems.item,4) when '2392' then (lineitems.quantity * lineitems.draft1099) end else case employees.type when '1099' then case left(lineitems.item,4) when '2391' then (lineitems.quantity * lineitems.draft1099) end else case employees.type when '1099' then case left(lineitems.item,4) when '3172' then (lineitems.quantity * lineitems.draft1099) end else case employees.type when '1099' then case left(lineitems.item,5) when 'ECAD2' then (lineitems.quantity * lineitems.draft1099) else (lineitems.quantity * lineitems.labor1099) end else case left(lineitems.item,4) when '2392' then (lineitems.quantity * lineitems.draft) else case left(lineitems.item,4) when '2391' then (lineitems.quantity * lineitems.draft) else case left(lineitems.item,4) when '3172' then (lineitems.quantity * lineitems.draft) else case left(lineitems.item,5) when 'ECAD2' then (lineitems.quantity * lineitems.draft) else (lineitems.quantity * lineitems.labor) end end end end end end end end as labor, (lineitems.quantity * lineitems.vehicle) as Vehicle, (activities.meals + activities.lodging) as PerDiem, (employees.state) as FState, (states.state) as TState from jobs inner join activities on jobs.jobid = activities.jobid left outer join employees on activities.employeeid = employees.employeeid left outer join lineitems on activities.activityid = lineitems.activityid inner join states on jobs.billingstateid = states.stateid where (activities.weekending = @Weekending or activities.transferdate = @Transfer or jobs.transferdate = @Transfer) and jobs.billingstateid <> employees.stateid and lineitems.quantity <> null GO
Can anyone help tell me why this happens or has anyone ever experienced this before?
Hi all,I have a sql puzzle (which I've solved) but seems very unwieldy. Isthere a more efficient way of doing this?It reads like so:SELECT a.*FROM axindex a INNER JOINaxindex b ON a.id4 = b.id4 AND a.id1 = b.id1 ANDa.id5 = b.id5 AND a.conflict_type = b.conflict_type INNER JOINaxindex c ON b.id4 = c.id4 AND b.id1 = c.id1 ANDb.id5 = c.id5 AND b.conflict_type = c.conflict_type INNER JOINaxindex d ON c.id4 = d.id4 AND c.id1 = d.id1 ANDc.id5 = d.id5 AND c.conflict_type = d.conflict_typeWHERE (a.stext = 'land') AND (b.stext = 'reg') AND (c.stext ='fees') AND (a.conflict_type = 5) AND (d.stext = 'hamilton')Many thanks in advance.Sam
I managed to get things broken and I can't solve it:
Scenario: SQL Server --->Databases --->TestDB ---> Users ------> dbo mapped to user1 (user1 = SQL Server user) ------> user1(database user) mapped to nothing (no SQL user) ---->Security -------> Logins -------> user1
Problem: I don't want SQL Server user 'user1' to be dbo but I can't remove dbo rights because it owns objects nor can I drop database user 'user1' or delete SQL Server user 'user1' because it's mapped to dbo.
I'm running in circles.. First and last time I use the auto_fix option to fix orphaned users. At this point, the only solution to me is dropping the database.
Here's a fun little puzzle that I thought up based on some rather gnarly problems I faced today. It might brighten up your morning. See if you can figure out what it does and how it does it.
How does it work? When will it fail as written (this example does not fail)? Why will it fail? How can you change it to increae its capacity?-- ptp 20080117 SQL puzzle
DECLARE @iINT , @cVARCHAR(360)
SET @i = 90 SET @c = ''
WHILE 64 < @i BEGIN SET @c = Char(@i) + Space(@i - 64) + @c SET @i = @i - 1 END
I've been running my simulations against our SQL Server 2005 database all day today and have been noticing a lot of deadlocking. Almost every transaction was deadlocking and retrying.
About an hour ago I started SQL Profiler and enabled trace flags using DBCC TRACEON(3605, 1204). However, even though Performance Monitor was indicating high Deadlocks/sec ratio I could not find any evidence of them in SQL Profiler or in SQL Server error log. I restarted my simulations several times, but nothing changed.
So, then a miracle happened and the last time I started my simulations all deadlocks disappeared. I did not change any queries and I am puzzled as to what could have happened. The only things I did were starting SQL Profiler and executing DBCC TRACEON commands.
Since queries and transactions did not change, could it be that they started executing (and hence releasing locks) faster because of lower network traffic at the end of the day or because the transaction log was written to disk? Could it be anything that SQL Server 2005 does to optimize its own performance? What else could have caused such a drastic change?
Hi,Probable there is a simple solution for this, hopefully someone candirect me in the right direction.I have a table with a persons firstname, lastname, birthdate andaddress. However, I want to select only one person per address, namelythe eldest of all persons living on the same address.Can anyone provide me a solution?Thanks in advance.Duncan
Hello -I have three feeds from sources around the world, each coming in at aseparate time.These feeds move into a large table (3GB) that is queried by managers.The feeds are loaded sequentially, and then the previous day's feedrows are deleted from the table (this is done so that the user'sapplication is never without data).The issue is that the import takes a lot of time, as do the deletes.These is hurting performance significantly. I attempted to fix theproblem by creating separate tables for each feed. I then created aview with the original table's name and used UNION ALL's. My intentionwas that as each feed came in, I'd alter the view with the new table'sname, and then truncate the older table. This met both goals ofconcurrency and import/delete speed.Unfortunately, this view seems to ignore the indexes on the underlyingtables, which devastates performance. I can't index the view, sincealtering it makes the index less useful.I'm looking for a different strategy for loading and deleting thedata, all without disruption to the applications. I'd appreciate anysuggestions....
I have probably a dumb newbie question but I can't find the answer anywhere.
On my Control Flow design pane I have two objects: a SQL task object and a Data flow task object. The first 'points' to the second. From my digging I believed that by indicating with the arrow from 1 to 2 that 1 would execute to finish before 2 was started.
My SQL taks is to truncate a table to receive the new data coming from the data flow task object. Instead 2 executes first and then 1. You can imagine producing an empty table was not my goal for this package.
I have a view based on few tables okay I have a query that gives me document details of various revisions Now i also get the latest rev of those if i use a MAX() in my Query
And if i filter for a month of 12/2004 If i query for latest it should give Document1 - rev2
Now i need to get a filter with latest as rev2, plus i also need as a indication of actual latest as rev 7 using a query
How do i do that ...
Coz when i use filter for a month and Max then it does not consider rev7 for that filter and skip the record and if i try to filter only as per month .. i get a wrong info as latest rev2 which is not a true info as per database records.
May i possibly get both ... filtered info as per specific month and also a rev info as what is the latest for a spec document.
Just i need to know is that possible .. in SQl using a query
Any more info needed please mail me on neeraj.jariwala@gmail.com
If I log into the SQL server with my credentials and connect to SSIS through SQL Management Studio, I'm able to execute my test SSIS package successfully.
If I go to my machine and try to connect to SSIS "Integration Services..." I get "Access Denied" message.
If I execute the same package via DTEXECUI, the package errors out also.
My account is member of the group "Security_IT_Data", this group has sysadmin privileges on the SQL Server level.
The account used to run the SSIS Service is part of the Local Admin group on the box and also a sysadmin on the SQL Server.
I have the following data (very simplified version)TransactionId Agent_Code------------- ----------191462 95328C205427 000024C205427 75547CAgent Code 75547C is a corporate agent. The others are not. I have alist of corporate codes so I can query against it, BUT what I want todo is...Return a unique TransactionId and max of the AgentCode, but if theAgent is a corporate agent, I need to return max of the corporate agentcodes. We can have multiple agents against the transaction andsometimes have a mix of corporate and none corporate agents. What weneed to do is see the corporate adviser if there is one. I only want 1record per TransactionId.We derive more data (sales hierarchy) from this, so are not interestedin anything other than the maximum, but need to know if it wascorporate which therefore gives me a different hierarchy later.Ideally I want to do this in a view and not use an SP. I can then usethis in my main view. If I have to resort to an SP, then so be it, butI would appreciate any helpful comments (or even better, the answer)ThanksRyan
Hi: in sp_add_jobschedule @freq_type = 8 for weekly @freq_interval = 1 for Sunday.
I need to script to create a job which will run every Sunday 6 pm, to backup one prod database to a new backup device. EX. database name is DBTest, and the 7/20/2003 (Sunday) running result should be backup_DBTest_2003_07_20.bak. and next Sunday's should be backup_DBTest_2003_07_27.bak. (if this way, the backup device will be created at the begining of the job running...)
this way the backup would not be overwritten. Is there a way to dynamically generate 54 weeks job script from this coming Sunday?
Or to make the backup result as backup_DBTest_2003_week27.bak, backup_DBTest_2003_week28.bak....
SQL 2000 pricing and licensing is quite confusing, and even more pain including the upgrading pricing from sql6.5/sql7.0.
I got very clear Windows2000 pricing and upgrading from one Microsoft web address. But just could not find a ONE Microsoft web sit explicitely describe the $ pricing for each of SQL2000 edition (per processor vs. CAL..., including upgrading from SQL 6.5, SQL 7.0). May be I missed that site?
I hope this is not outside the scope of this newsgroup.have been useing BulkAdd with ms-access with good success.Until now that I;m switching to MSDE which is on a shared server.The problem is that MSDE gives an error:Invalid attribute/option identifier, sql state=HY092which corresponds to:SQL_ATTR_CONCURRENCY statement attribute was set to SQL_CONCUR_READ_ONLY.but my BulkAdd routine explicitly has a call to:SQLSetStmtAttr(stHndl, SQL_ATTR_CONCURRENCY, SQL_CONCUR_LOCK , iRet)I cannot imagine how the concurrency is readonly.Could it be that I'm looking at the wrong place or the wrong attribute???Thanks in adv.
