We are setting up a new SMS (SP3) primary server with local SQL 7.0 (SP3) and are mandated to use the following configuration for hard drives:
C:&D: are on mirrored 18GB drives (2 channel RAID controller)
C: OS (8GB) partition
D: pagefile & temp (10GB)
E:&F: are on RAID 5 (3x36GB) 72GB useable (2 channel RAID controller)
E: temp data for field techs (10GB)
F: SMS & SQL (62GB)
We would like to have the SQL logs on a different physical drive than the data (for improved performance), so we are planning on putting it on drive D:
We realize this will affect redundancy, but should this be a big concern?
We have to setup a SQL 2000 box (it's what the app requires) and I would like to know what is considered the best practice for setting up the drive fault tolerance.
We are looking at allocating about 500GB for both system and data drives.
Hi, I'm using SqlServer 2000 at a web host, and share this db with about 200 others (it's an inexpensive web host so I guess there is not very much traffic on most of them, but I can only see their db IDs so I don't know what organisations they are or the urls they have). Usually, my web site runs fine, but lately it's really really slow (as in non-functioning in reality). I'm in the middle of coding my seach functionality, so I fear that I'm causing this myself. ;-) However, there are only ten posts in my test db, so it should be a piece of cake for SqlServer. The other .aspx pages runs rather slow too now, but when I start a programming session they have worked fine (this has happened three times now so I don't know what to conclude). When the server slows down, it can stay down for hours. I've also tried to access my site from other computers but get the same result, so I guess it has nothing to do with caching (I also tried emptying my browser's - IE7 - cache). I have several asp web sites with Access dbs at the same web host, and they all work fine. In addition, I have no problem viewing and editing my db at the web host via SqlServer Management Studio Express. What do you think of this problem? Should I go for a more expensive web host? Should I give them the "It's not you, it's me" speech? ;-) Or is it really me? Below's my search code, and as you can see, there are some integers there to make sure the loops don't run ad infinitum. I don't really think the problem actually lies with my search code, as everything worked well a week ago, but I don't know... BTW, if you think my code can run much faster without getting too tricky, please let me know. Please help! Pettrer If Page.IsPostBack Then Dim soktext As String = Trim(SokTextBox.Text) + " " Dim tillagg As String = "" Dim varv As Integer = 1 Dim loopvarv As Integer = 0 soktext = Replace(soktext, vbCrLf, " ") 'vbCrLf = mellanslag i VB-terminologi Do While InStr(soktext, " ") And varv < 100 soktext = Replace(soktext, " ", " ") loopvarv += 1 Loop While Trim(soktext).Length > 0 And varv <= 20 'Loop för att identifiera ord och lägga till dem i sqlsatsen, ett i taget Dim icke As String = " "
Dim ordet As String = soktext.Substring(0, soktext.IndexOf(" ")) 'Det godkända ordet läggs till i tmp-strängen soktext = soktext.Remove(0, soktext.IndexOf(" ") + 1) 'Tar bort det som just använts (eller varit för kort) If ordet.Length > 1 Then 'Blanksteg och ensamma bokstäver förhindras att komma med i sql-satsen If ordet.StartsWith("-") Then 'Det är ett minusord icke = " NOT " 'I sqlsatsen blir det alltså AND NOT (...) ordet = ordet.Remove(0, 1) 'Tar bort minusttecknet från söksträngen End If tillagg += " AND " & icke & " (artistnamn LIKE '%" & ordet & "%' OR " & _ "beskrivning LIKE '%" & ordet & "%' OR " & _ "kulturhemvist LIKE '%" & ordet & "%' OR " & _ "kontaktnamn LIKE '%" & ordet & "%') " End If varv += 1 End While Dim kat As String = "" If KategoriDropDownList.SelectedValue <> 0 Then kat = " AND (kategori1 = " & KategoriDropDownList.SelectedValue & _ " OR kategori2 = " & KategoriDropDownList.SelectedValue & _ " OR kategori3 = " & KategoriDropDownList.SelectedValue & _ " OR kategori4 = " & KategoriDropDownList.SelectedValue & ") " End If Dim lan As String = "" If LanDropDownList.SelectedValue <> "alla" Then lan = " AND lan = '" & LanDropDownList.SelectedValue & "' " End If ResultatDataSource.SelectCommand = "SELECT DISTINCT TOP 200 folknuID, kategori1, kategori2, kategori3, kategori4, initial, artistnamn, lan, kontakthemsida, kategori1, (COALESCE(audio1,'') + COALESCE(audio2,'') + COALESCE(audio3,'')) As audio, (COALESCE(video1,'') + COALESCE(video2,'') + COALESCE(video3,'')) As video, (COALESCE(pressbild1,'') + COALESCE(pressbild2,'') + COALESCE(pressbild3,'')) As pressbild " & _ "FROM Mytable " & _ "WHERE shown = 1 AND shownadm = 1 " & _ tillagg & _ kat & _ lan & _ "ORDER BY initial" ResultatLabel.Text = "SelectCmd: " & ResultatDataSource.SelectCommand ResultatGridView.DataBind() End If '*** på sökkoden
When I open my Computer Management Console and look at Event Viewer/Application, I see an error:
Fault bucket 277816039.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Data: 0000: 42 75 63 6b 65 74 3a 20 Bucket: 0008: 32 37 37 38 31 36 30 33 27781603 0010: 39 0d 0a 9..
What is this error and how can I fix it?
Also, When I click on the link to get more information all I get is a page that says there is no information on this error. This happens with ALL my errors! I have never seen any usefull information from this link.
I am running on a Windows 2004 Standard Server with SQL Server 2005.
I just installed SQL7.0 on a windows 98 machine. When I go to register a new database, I get the following message. MMC caused a general protection fault
I have rebooted the machine, removed & reinstalled the application.
I,m having a problem with something that worked well just recently. maybe I changed a setting by mistake.
I have a Database grid view on my page. When I configure my data sourse and get to "configure the select statment " on the wizard and press advanced to open the wizard with two check boxes " generate INSERT, UPDATE etc and the other check box "Use optimistic concurrency " this input box of the wizard is greyed out (non responsive)
My raid-5 server failed because one disc, some folders and files are lost, but I have recoverd whit some software for the propuse.
I have reinstaled the SQL server and attached the database, but when I try to open the tables I recieve a error message saying : internal consisty error... somethihg like this.
Codes represent different skills of people, example the sort of job functions they’ve held in their employment. Like:
t-CEO, t-CFO t-Founder etc.
people, clearly holds data about people.
CodedPeople holds data about which people are coded. So person1 can be coded as t-CEO as well t-Founder, and person2 coded as t-CFO
What I need is a query that returns all distinct people records and takes a number of codeNames as input. So if I throw in t-CEO OR t-Founder I get person1, again if I define t-CEO AND t-Founder I get person1.
However when I add t-CEO OR t-CFO I get person1 and person2 but when the query takes t-CEO AND t-CFO I get no result.
I can’t seem to come up with anything that would give me a good starting point. Is there a design fault here? All opinions are much appreciated, thanks in advance!
but I can't figure ou how to "remove" the RSClientPrint Class add-on in IE 6.
P.S. Why does something as simple as printing need to be an ActiveX control? There are all kinds of issues revolving around pushing this out using SMS for users without local admin permission.
I've some really big problems with SQL Server 2005 Express. I Recently, I had 2 instances on my machine, one was a SQL Server 2000 Developer and the other a 2005 Express Version. The 2000 version was not necessary anymore, so I unstalled it, since then, the Express version keeps having probems.
Under the Server Properties -> Database settings -> Database Locations I've changed the path to D:sqldatamssql, but now, the programm always takes the "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData" to store and load the databases, no matter what I do.
And there are more problems see here:
Restoring does not work======================= I wanted to restore a database called "fw40_admin" from a backup file (.bak), but that didn't work at all, it always says this as an error:
"System.Data.SqlClient.SqlError: The operating system returned the error '5(error not found)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:sqldatamssqlfw40_admin.mdf'. (Microsoft.SqlServer.Express.Smo)"
But as soon as I type in the installation path "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData" inside the "Restore As" under "Options", it works.
Why does the backup only work in "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData", BUT the database locations under the server properties is indicated with "D:sqldatamssql"????
Attaching does not work======================= Attaching an existing database does not work either. Most databases (mdf) are located on the path D:sqldatamssql, but as soon as I press
Attach Databases -> Add
The dialog window appears with the "D:sqldatamssql" path, but it is unable to find any *.mdfs altough THERE ARE .mdfs in this directory. But Express can find databases in the selected installation: "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData". Why can't Express see the directory on the D: partition???
Hi everyone:We received a error message "Log File to Database is Full. Backup thetransaction log to free up space."I have a Access 2000 application that calls a Stored Procedure thatinserts about 5000 records into a worktable on a SQL server 8.0database table. After the user is finished with the work table astored procedure deletes just the records that he was using in thework file (so I can't use Truncate Table). I have the work tablelinked to an Access database so I can't use a temporary table on theserver.This action of 5000 records being added and deleted to this workfilecan occur 7 or 8 times an hour. And for the last week I've beentesting the application so I have probably been doing this action 10to 20 times an hours.I do not currently use a COMMIT with the INSERT Stored Procedure orthe DELETE Stored Procedure.Although I didn't think this was a lot of records, could it be that myapplication caused this error. Is there a way to find out for sure ifmy application caused this error and if there something I can do in myapplication to keep the Transaction Log from filling up.Thank you for taking the time to read my post and any help would beappreciated.
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 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
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.
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:
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?
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.
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?
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.).
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
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.
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.
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?
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.
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.
1. where do we see the buffer cache hit ratio. can we set the buffer catche hit ratio manually. 2.In query execution plan we execute the query for performance issue.which parameters we check to take an action?
I build a query in SQL-server 2000 but i'm not happy with the performance, it takes about 15 minutes to execute the query (4 min INSERT and 11 min UPDATE). The table tbl_total has 3 million records and an index on Contract and Item, the table contracts has 1 million records and a key on Contract and Item. How can I speed up this query, is it for example possible to put an index on @table (internal table)?
INSERT INTO @table SELECT TOT.Contract, TOT.Item, MAX(TOT.Change_date) FROM tbl_total TOT WHERE EXISTS (SELECT 'X' FROM contracts CONT WHERE TOT.Contract = CONT.Contract AND TOT.Item = CONT.Item) GROUP BY TOT.Contract, TOT.Item
UPDATE contracts SET contracts.Change_date = TT.Change_date FROM contracts INNER JOIN @table TT On contracts.Contract = TT.Contract AND contracts.Item = TT.Item
