Server Optimization
Mar 5, 2008
Hi All,
Were I work we have a standalone system that writes information to an event log. Currently this event log is in .mdb (MS Access) format. The problem we have is that the .mdb seems to get very slow to access after 100,000 rows or so, so it needs to be cleared out regularly. We have long discussed using an SQL server to log the events to instead of an .mdb file.
I have written a VB program to test the two DB formats and i expected MS SQL server 2005 to be faster at reading/writing than the .mdb. Both the server and the .mdb are local to the system (it's a standalone system), so we know it's not network that is making the SQL server slower. So here is my question: does anyone know of any good tips/tricks in the server configuration options to speed it up/generally improve performance?
The table definitions are the same in both SQL server and the .mdb file:
Table:event_log_0000_000000
Module - Text
Event_date - Text
Event_Time - Text
Event - Text
Record_Number - int, primary key
I know it would probably be better to have Event_date and Event_Time as datetime types, but I’m not in charge of that decision. The data/table doesn't matter to much i just need to prove that the SQL server is better (and faster) than a .mdb file.
The VB program uses DAO to access the .mdb DB and ADODB to access the SQL server - this is the only difference to how the DB's are accessed and I don't think it would account for the slowness of the SQL server.
This is my first post here, so I’ve probably missed out some vital information, so please ask.
Also sorry if this is the wrong place to post this question, it sort of covers Access/SQL Server 2005/Database programming areas, so wasn't sure.
Thanks
View 14 Replies
ADVERTISEMENT
Apr 10, 2008
Hello Everybody,
I have a small tricky problem here...need help of all you experts.
Let me explain in detail. I have three tables
1. Emp Table: Columns-> EMPID and DeptID
2. Dept Table: Columns-> DeptName and DeptID
3. Team table : Columns -> Date, EmpID1, EmpID2, DeptNo.
There is a stored procedure which runs every day, and for "EVERY" deptID that exists in the dept table, selects two employee from emp table and puts them in the team table. Now assuming that there are several thousands of departments in the dept table, the amount of data entered in Team table is tremendous every day.
If I continue to run the stored proc for 1 month, the team table will have lots of rows in it and I have to retain all the records.
The real problem is when I want to retrive data for a employee(empid1 or empid2) from Team table and view the related details like date, deptno and empid1 or empid2 from emp table.
HOw do we optimise the data retrieval and storage for the table Team. I cannot use partitions as I have SQL server 2005 standard edition.
Please help me to optimize the query and data retrieval time from Team table.
Thanks,
Ganesh
View 4 Replies
View Related
Jun 9, 2008
Hi all, I was wondering if you's could help me out.
We run sql optimization on 2 of our databases and when they run they cause our server to crash - system log reports: unexpected shutdown. When we take these jobs out of action the server does not crash.
I'm not sure if this could be a CPU or memory related issue or if its a SQL configuration issue?
Any comments/ideas would be great.
Thanks.
View 20 Replies
View Related
Aug 16, 2006
It is said that, aimed at the same task, different SQL Select Statements maybe have different efficiency. Microsoft Corp. has provided the tool,Query Analyzer, but I have not any idea.
Thanks.
View 4 Replies
View Related
Jul 2, 2001
Hi, everyone,
I have a SQL2000(SP1)/Windows2000, and a database - 4GB. The server physical memory is 1GB, and about 900MB is allocated to the SQL2000 through the memory dynamical configuration screen.
A scheduled database maintenance job was created using the standard maintenance planner tool. In this job, only Updata Data Optimization was included. Everytime, this job freezed the whole server - all screens, SQL client accesses, and, even the keyboard. A server machine reboot had to be made.
My understanding about Optimizatoin is that it does reindex for each index of each table in the database. So I picked the largest table in the database - about 1.5 million rows - to run DBCC DBREINDEX manually against each index one at a time, using the Query Analyzer. They were all running through, without causing server lockup. However, I noticed that each time, the DBREINDEX statement made the CPU and Physical I/O climbed extreamlly high. I suspected that, since the Optimization job put all tables together to DBREINDEX, it might exhaused the system resource that caused the server problem, but I am not 100% sure, neither do I know where to confirm this.
Confusing enough, other databases on the same server didn't have the problem. They all have maintance plan and run ok. I did all sorts of DBCC checks against this database which I had problem with, they were no error reported. I even loaded the database, using the backup, to another SQL 2000 server, and run Optimization there, and it had no problem.
I am not sure it is a server issue? or a database issue? or a SQL bug? Anybody had same experience?
Appreicate!
Regards! Yan Li
View 1 Replies
View Related
Jan 7, 2006
I posted this on the .NET Framework inside Sql Server forum as well. Sorry if the cross-post offends anybody.
I upgraded my primary production server this morning to SQL 2005. Everything went fairly smoothly, but a couple of hours after my installation was complete, I found the following error in my event log:
Source: .NET Runtime Optimization Service
EventID: 1101
.NET Runtime Optimization Service (clr_optimization_v2.0.50727_32) - Failed to compile: Microsoft.ReportingServices.QueryDesigners, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 . Error code = 0x80070002
I am a little stumped since we did not install Reporting Services. We only installed Database Services, Integration Services and Workstation Components. I'm open to any suggestions on this. This does not seem to be negatively affecting our server, but I do want to resolve it as soon as possible.
Thanks,
Kevin
View 47 Replies
View Related
Nov 26, 2014
We are planning to 2014 migration in few days.
ServerA----- ServerA1
ServerB---- ServerB1
In serverA we have 5databases. And making 5databases as availability group. The replica is ServerA1
In server B we have 3 databases. And the making those 3 databases as an availibility group. The secondary replica is ServerB1.
What is the best option to configure the quoram drive in this situation.
Also Server A1 & Server B1 also we use for reporting purposes.
We have some sensitive data. Is it possible to delete the data while reading the data?
How the memory optimization feature work with always on?
View 8 Replies
View Related
Jun 6, 2007
Dear Advance,I used one stored procedure to retrive 3 different result set. and in the codebehind i seperate it. means from the dataset i seperate three different datatable and then show my data as my need.but the main problem is ... after retriving the datafrom the database i have to user foreach loop to bind the coulmns data to my different custom class.example: foreach (DataRow oDrow in MyDataTable.Rows) {oClass=new Class();oClass.Name1=oDrow["Name1"] .toString();oClass.Name2=oDrow["Name2"] .toString();.... } 1. so my first question is there any optimization possible ?2. my result set is too loong ... so should keep just one hit to database or hit more than one time Currently i am optimizing my web application. in the previous version 1 have to hit the database 3/4 times for different purposes. but now it hits only one time... but it takes time in the codebehind to perform different operation.Any Suggestion
View 1 Replies
View Related
Dec 11, 2001
I have a SP that calls about 10 stored procedures sequentially. The 10 SP's are basically complex update statements, each one individual. Is there any way to optimize this?
I know putting the 10 into 1 SP would make it compile faster but thats about it. Are there any execution tricks of Stored Procedures firing off sequentially?..or anything I should know?
-thanks
Bartman
View 2 Replies
View Related
Mar 2, 2001
Hello All,
What is the best way to optimize this code or rewrite it using ISNULL ?
CREATE PROCEDURE get_employees (@dept char(8), @class char(5))
AS
IF (@dept IS NULL AND @class IS NOT NULL)
SELECT * FROM employee WHERE employee.dept IS NULL
AND employee.class=@class
ELSE IF (@dept IS NULL AND @class IS NULL)
SELECT * FROM employee WHERE employee.dept IS NULL
AND employee.class IS NULL
ELSE IF (@dept IS NOT NULL AND @class IS NULL)
SELECT * FROM employee WHERE employee.dept=@dept
AND employee.class IS NULL
ELSE
SELECT * FROM employee WHERE employee.dept=@dept
AND employee.class=@class
Thanks in advance.
-Praveena
View 1 Replies
View Related
Jul 6, 2001
I am wondering if the size of the data file makes a difference in running Insert's and/or doing Fetch's. Our DB was 11GB in size, I ran a dbcc shrinkdatabase and it shrank it to 5.5 GB in size, now that it is smaller will it run a select query faster as opposed to when we run large inserts and it has to automatically grow to accommodate the insert. I am trying to figure out if I should leave my .mdf file large or keep it small or does it even make a difference. I am only doing large inserts while loading data to get ready for production after that the inserts will be hourly but much smaller, however our queries to the DB after it is in production will be much more intensive.
Hope this makes sense. Appreciate your help.
View 2 Replies
View Related
Jan 9, 2006
Hi All,
I am writing a script that runs the optimization process. I am not sure how to test it? Any help is greatly appreciated.
View 9 Replies
View Related
Dec 20, 2006
Hello,
Is there a tool to optimize SQL procedures code?
Thanks,
Miguel
View 4 Replies
View Related
Jul 20, 2005
We're building a company wide network monitoring systemin Java, and need some advice on the database design andtuning.The application will need to concurrently INSERT,DELETE, and SELECT from our EVENT table as efficiently aspossible. We plan to implement an INSERT thread, a DELETEthread, and a SELECT thread within our Java program.The EVENT table will have several hundred million recordsin it at any given time. We will prune, using DELETE, aboutevery five seconds to keep the active record set down toa user controlled size. And one of the three queries willbe executed about every twenty seconds. Finally, we'llINSERT as fast as we can in the INSERT thread.Being new to MSSQL, we need advice on1) Server Tuning - Memory allocations, etc.2) Table Tuning - Field types3) Index Tuning - Are the indexes right4) Query Tuning - Hints, etc.5) Process Tuning - Better ways to INSERT and DELETE, etc.Thanks, in advance, for any suggestions you can make :-)The table is// CREATE TABLE EVENT (// ID INT PRIMARY KEY NOT NULL,// IPSOURCE INT NOT NULL,// IPDEST INT NOT NULL,// UNIXTIME BIGINT NOT NULL,// TYPE TINYINT NOT NULL,// DEVICEID SMALLINT NOT NULL,// PROTOCOL TINYINT NOT NULL// )//// CREATE INDEX INDEX_SRC_DEST_TYPE// ON EVENT (// IPSOURCE,IPDEST,TYPE// )The SELECTS areprivate static String QueryString1 ="SELECT ID,IPSOURCE,IPDEST,TYPE "+"FROM EVENT "+"WHERE ID >= ? "+" AND ID <= ?";private static String QueryString2 ="SELECT COUNT(*),IPSOURCE "+"FROM EVENT "+"GROUP BY IPSOURCE "+"ORDER BY 1 DESC";private static String QueryString3 ="SELECT COUNT(*),IPDEST "+"FROM EVENT "+"WHERE IPSOURCE = ? "+" AND TYPE = ? "+"GROUP BY IPDEST "+"ORDER BY 1 DESC";The DELETE isprivate static String DeleteIDString ="DELETE FROM EVENT "+"WHERE ID < ?";
View 2 Replies
View Related
Jul 17, 2007
There are two main tables in my app,in order to optimize search via scope condition, I set many indexs for these two tables
however,at the same time the two tables are also used for my etl app,everyday there are more than thousands of data need to be updated or inserted, but index is not suitable for huge modification,any idea about how to handle this?
thanks in advance
View 4 Replies
View Related
Oct 15, 2007
Hello,
what is the meaning about <MissingIndexGroup Impact="99.9521"> in the Queryplan? Should I create a Grouped Index? An what is the meaning about Impact="99.9521"?
If the Impact =100 you get a 100% better performance, and if the impact =20 ypu get a 20% better performance, is this the meaning?
finchen
View 4 Replies
View Related
Aug 15, 2007
Hi,
Can anyone help me optimize the SELECT statement in the 3rd step? I am actually writing a monthly report. So for each employee (500 employees) in a row, his attendance totals for all days in a month are displayed. The problem is that in the 3rd step, there are actually 31 SELECT statements which are assigned to 31 variables. After I assign these variable, I insert them in a Table (4th step) and display it. The troublesome part is the 3rd step. As there are 500 employees, then 500x31 times the variables are assigned and inserted in the table. This is taking more than 4 minutes which I know is not required :). Can anyone help me optimize the SELECT statements I have in the 3rd step or give a better suggestion.
DECLARE @EmpID, @DateFrom, @Total1 .... // Declaring different variables
SELECT @DateFrom = // Set to start of any month e.g. 2007-06-01 ...... 1st
Loop (condition -- Get all employees, working fine)
BEGIN
SELECT @EmpID = // Get EmployeeID ...... 2nd
SELECT @Total1 = SUM (Abences) ...... 3rd
FROM Attendance
WHERE employee_id_fk = @EmpID (from 2nd step)
AND Date_Absent = DATEADD ("day", 0, Convert (varchar, @DateFrom)) (from 1st step)
SELECT @Total2 ........................... same as above
SELECT @Total3 ........................... same as above
INSERT IN @TABLE (@EmpID, @Total1, ...... @Total31) ...... 4th
Iterate (condition) to next employee ...... 5th
END
It's only the loop which consumes the 4 minutes. If I can somehow optimize this part, I will be most satisfied. Thanks for anyone helping me....
View 11 Replies
View Related
Jun 23, 2004
Hi guys,
Could any one tell me what is the best way to declare a connection from ASP .net to a SQL database so the sql could support the maximum users, because it seems that the way i'm using is not correct cuz when i make some transactions from my website to the database, the database send an error message saying that there are no more free connections.
Thnx
View 1 Replies
View Related
Apr 28, 2000
This may sound a little silly, but does anyone have any words of wisdom on how to optimize a server/database for minimim rollback? We have some multimillion row tables we were trying to do updates against, and after several days they increased the size of the transaction log to the point they filled up the drive the database files/logs were on. We've now been running a rollback for about five days. I'd like to make sure this doesn't happen again.
View 1 Replies
View Related
Apr 19, 2000
I am using the Database maintenance on a database that is about 4gb. The database optiiztion is running about an hour. Does this job only do an update stats? If I run the stored procedure sp_updatestats on the database it only takes a couple of minutes. Are thes two processes doin the same thing? Do I need them if the create, update statistics are turned on?
Thanks,
Glen
View 4 Replies
View Related
Feb 19, 2001
Trying to optimize a query, and having problems interpreting the data. We have a query that queries 5 tables with 4 INNER JOINS. When I use INNER HASH JOIN, this is the result:
(Using SQL Programmer)
SQL Server Execution Times:
CPU time = 40 ms, elapsed time = 80 ms.
Table 'Table1'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'Table2'. Scan count 1, logical reads 40, physical reads 0, read-ahead reads 0.
Table 'Table3Category'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'Table4'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'Table5'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0.
When I use INNER JOIN, this is the result:
SQL Server Execution Times:
CPU time = 10 ms, elapsed time = 34 ms.
Table 'Table1'. Scan count 4, logical reads 10, physical reads 0, read-ahead reads 0.
Table 'Table2'. Scan count 311, logical reads 670, physical reads 0, read-ahead reads 0.
Table 'Table3'. Scan count 69, logical reads 102, physical reads 0, read-ahead reads 0.
Table 'Table4'. Scan count 69, logical reads 98, physical reads 0, read-ahead reads 0.
Table 'Table5'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0.
Now, when timing the code execution on my ASP page, it's "faster" not using the HASH. Using HASH, there are a few Hash Match/Inner Joins reported in the Execution Plan. Not using HASH, there are Bookmark Lookups/Nested Loops.
My question is which is better to "see": Boomark Lookups/Nested Loops or Hash Match/Inner Joins for the CPU/Server?
Thanks.
View 1 Replies
View Related
Mar 14, 2003
IS there any way to rewrite this Query in optimized way?
SELECT dbo.Table1.EmpId E from dbo.Table1
where EmpId in(
SELECT dbo.Table1.EmpId
FROM (SELECT DISTINCT PersonID, MAX(dtmStatusDate) AS dtmStatusDate
FROM dbo.Table1
GROUP BY PersonID) derived_table INNER JOIN
dbo.Table1 ON derived_table.PersonID = dbo.Table1.PersonID AND
derived_table.dtmStatusDate = dbo.Table1.dtmStatusDate))
Thanks....j
View 1 Replies
View Related
Mar 7, 2006
How can I optimized the following query:
(SELECT e.SID
FROMStudents s
JOINTable1e ON e.SID= s.SID
JOINTable2 ed ON ed.Enrollment = e.Enrollment
JOINTable3 t ON t.TNum = e.TNum
JOINTable4 bt ON bt.TNum = t.TNum
JOINTable5 b ON b.Batch = bt.Batch
JOIN IPlans i ON i.IPlan = ed.IPlan
JOINPGroups g ON g.PGroup= i.PGroup
WHERE t.TStatus= 'ACP'
ANDed.EStatus= 'APR'
ANDe.SID=(select distinct SID from Table1 where Enrollment=@DpEnrollment))
AND(ed.EffectiveDate=
(SELECT EffectiveDate
FROM Table2 ed JOIN Table1 e
ON e.enrollment=ed.enrollment
WHERE IPlan = @DpIPlan
ANDTCoord = @DpTCoord
ANDAGCoord= @DpAGCoord
ANDDCoord=@DpDCoord )
ANDDSeq= @DpDSeq)
ANDe.SID=
(select distinct SID from Table1 where Enrollment=@DpEnrollment))
)
ANDed.TerminationDate=
(SELECT TerminationDate
FROM Table2 ed JOIN Table1 e
ON e.enrollment=ed.enrollment
WHERE IPlan = @DpIPlan
ANDTCoord = @DpTCoord
ANDAGCoord= @DpAGCoord
ANDDCoord= @DpDCoord )
ANDDSeq= @DpDSeq)
ANDe.SID=
(select distinct SID from Table1 where Enrollment=@DpEnrollment))
)
))
View 2 Replies
View Related
Mar 20, 2006
DECLARE @PTEffDate_tmp AS SMALLDATETIME
SELECT @PTEffDate_tmp = DateAdd(day, -1, PDate)
FROM PDates pd WHERE iplan = @DIPlan and pd.TCoord = @DTCoord and DType = 'EF'
DECLARE @PTCoord_tmp as char(3)
SELECT @PTCoord_tmp = tc.TCoord
FROM PDates pd JOIN TCoords tc ON (pd.TCoord = tc.TCoord)
WHERE pd.Iplan = @DIPlan and tc.TGroup = @TGroup_tmp
and PDate = @PTEffDate_tmp and DateType = 'TR1'
DECLARE @EStatus_tmp as char(3)
SELECT @EStatus_tmp = EDStatus From EDetails ed
JOIN ENR e ON (ed.enr = e.enr)
JOIN Trans t ON (e.transID = t.TransID)
WHERE iplan = @DIPlan
and ed.TCoord = @PTCoord_tmp
and t.TransS= 'ACP'
and DCoord = @DCoord
and CEnr is null
View 3 Replies
View Related
Aug 17, 2006
How can I optimazed my query. Since my DB is more then 1 mln it takes a while to do all those join?
select *
FROM EEMaster eem
JOIN NHistory nh
ON eem.SNumber = nh.SNumber
OR eem.OldNumber = nh.SNumber
OR eem.CID = (Replicate ('0',12-len( nh.SNumber))+ nh.SNumber )
View 4 Replies
View Related
Apr 7, 2006
Well i wanted to prove to some guys that cursors are not really that important:shocked: .
:D So this code is suppose to remove duplicate tuples from a table without temporary tables or cursors:D. Except it needs some optimization(and alot of system down time, not sure about that:confused: ).
I would like it, if some one could find an instance of the table when the below code fails or some way to optimize the code or anything;) .
--trashtable for real data
create table abc
(col1 tinyint,
col2 tinyint,
col3 tinyint)
--trash values for trash table
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (3,2,1)
insert into abc values (2,2,3)
insert into abc values (3,2,4)
--check that there are ten rows
select * from abc
--check that there are only five distinct rows
select distinct * from abc
--run code : next 15 line as a batch
declare @lp tinyint
declare @col1 tinyint,@col2 tinyint,@col3 tinyint
set @lp=1
while @lp>0
begin
if not exists (select top 1 * from abc group by col1,col2,col3 having count(col1)>1)
set @lp=0
else
begin
select top 1 @col1 = col1,@col2 = col2,@col3 = col3 from abc group by col1,col2,col3 having count(col1)>1
delete from abc where col1=@col1 and col2=@col2 and col3=@col3
insert into abc values(@col1,@col2,@col3)
end
end
--only distinct values left in trash table
select * from abc
--think code can be optimized
--just wanted to prove: can be done without cursors or temporary tables
View 3 Replies
View Related
Oct 9, 2006
Sql 2003
Database Optimization not getting done.
We have a weekly maintenance window and a database that we cannot get optimization to finish timely.
In paticular we have a 70 gig table (indexes included) and the optimization fails because of log file filling up or just runs plain to long (8+ hours)
The table is part of a 3rd party transactional database that cannot be motified.
Is this just the way it has to be or our we missing something.
View 5 Replies
View Related
Mar 30, 2008
Generally speaking when you want to optimise an application that relies on a database which is the order of the following optimization techniques
a) optimizing the spread of the pysichal elements of the database on different disks of the server
b) optimizing the use ot the RAM
c) optimizing the SQL
d) opimizing the OS
Thank you,
Ronnyy
View 4 Replies
View Related
Apr 4, 2008
Hey guys,
My company is undertaking a database optimization project. Optimization the schema, the code, etc. I would like to ask, if you guys could help out, the following:
1. What risks are there? What are the pitfalls?
2. My company is hesitant to do a database freeze and stop all new development until our vendor (who's restructuring tables and changing database objects) has a stable database for us to obtain, then, and only then can we continue development on this newer copy. My question to this: how can we either reduce the database code freeze or work in parallel?
3. Can anyone point me to other sources of information? Another thread? A book? A URL?
Thanks a bunch!
View 11 Replies
View Related
Feb 9, 2004
I have this problem with my optimization job seems to fail all the time.
I have this set up as a sql maintenance plan and this is run 1 every week. i have checked for things that could comme in conflict but theirs nothing. here is the error i am getting from the job history step.
Executed as user: SAPCORPadminsg. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
PLZ HELP>
View 3 Replies
View Related
Mar 31, 2004
Hello All,
I have ran one query on 'A' server there it takes 10 sec and if I run same query 'B' server then it takes 22 sec.
In both server, I have creted few index (as query needs).
Is anyone can help me what is happening.
Regards,
M. J.
View 4 Replies
View Related
Apr 4, 2008
Hey guys,
My company is undertaking a database optimization project. Optimization the schema, the code, etc. I would like to ask, if you guys could help out, the following:
1. What risks are there? What are the pitfalls?
2. My company is hesitant to do a database freeze and stop all new development until our vendor (who's restructuring tables and changing database objects) has a stable database for us to obtain, then, and only then can we continue development on this newer copy. My question to this: how can we either reduce the database code freeze or work in parallel?
3. Can anyone point me to other sources of information? Another thread? A book? A URL?
Thanks a bunch!
View 4 Replies
View Related
Apr 23, 2008
I work on tables containing 10 million plus records.
What are the general steps needed to ensure that my queries run faster?
I know a few:
- The join fields should be indexed
-Selecting only needed fields
-Using CTE or derived tables as much as I can
-Using good table reference
eg
select a.x , b.y
from TableA a inner join TableB b
on a.id = b.id
I will be happy if somebody could share or add more to my list.
Regards to all
View 4 Replies
View Related
