| Author |
Topic |
|
chriztoph
Posting Yak Master
184 Posts |
 Posted - 2010-03-17 : 22:25:08
|
| why this SQL Server 2008 always high percentage of using the memory?In Activity Monitor the % Processing Time sometimes went to 90% to 99%..what was the problem why this so high..In Windows Task Manager sqlservr.exe, the Memory Usage is always in 1.7 million K.. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-17 : 22:27:44
|
| By design, SQL Server will use all the memory you allow it -- and not return it to the free store. This dramatically increases performance.Remember that SQL Server is intened to be THE ONLY application running on the server.You can restrict the amount of memory SQL Server will use, by setting the MAX SERVER MEMORY optionby the way, this isn't just SQL 2008. Has been true at least since 7.0 and is also true in other high end database applications like DB2 |
 |
|
|
chriztoph
Posting Yak Master
184 Posts |
Posted - 2010-03-17 : 22:33:40
|
| and why some applications that we are using are get timeout expired when viewing of reports? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-17 : 22:38:01
|
| That sounds like poorly designed queries/indexes.Post the table DDL and queries in question. We'll help ya straighten 'em out. |
|
|
|
chriztoph
Posting Yak Master
184 Posts |
Posted - 2010-03-17 : 22:51:39
|
| how to reindex all tab;es and views? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-17 : 23:26:48
|
| let's figure out what your performance problems are 1st...show us some of the queries that time out...Let me say though that indexes are THE key to performance in 99.999% of cases.Let's see the offending queries 1st, then we can help ya get where you need to be. |
|
|
|
chriztoph
Posting Yak Master
184 Posts |
Posted - 2010-03-17 : 23:40:51
|
when i execute this on management studio,i'm not getting timeout expired but it takes a long time.here is my query:SELECT RTRIM(ISNULL(tbl_SeamanMain.LastName, '')) + ', ' + RTRIM(ISNULL(tbl_SeamanMain.FirstName, '')) + ' ' + RTRIM(ISNULL(tbl_SeamanMain.MiddleName, '')) AS SeamanName, tblPayroll_Allottees.PayPeriod, tblAdmin_Vessels.Vessel, round(tblPayroll_Allottees.NetAllotment,2) as NetAllotment , tblPayroll_Allottees.AccountNo, tblPayroll_Details.Included, tblPayroll_Allottees.SeamanID, tblAdmin_Principals.Principal, tblAdmin_Periods.MonthText, tblAdmin_Positions.Officer, tblPayroll_Allottees.PaymentType, tblPayroll_VesselLog.PrincipalCode, tblPayroll_VesselLog.PayrollType, tblPayroll_VesselLog.Closed, tblAdmin_Branches.BranchName, tblAdmin_Banks.BankName AS Mybank, tblAdmin_Banks.BankName, tblData_Allottees.Allottee, tblPayroll_Details.Vesselcode, tblPayTypeRefAllot.Description , tblPayTypeRefAllot.Description as MYDesc, tblAdmin_Positions.IDPosDep FROM tblPayTypeRefAllot INNER JOIN tblData_Allottees INNER JOIN tblAdmin_Periods INNER JOIN tblPayroll_VesselLog INNER JOIN tblPayroll_Allottees ON tblPayroll_VesselLog.PayrollID = tblPayroll_Allottees.PayrollID ON tblAdmin_Periods.MonthCode = tblPayroll_Allottees.PayPeriod INNER JOIN tblAdmin_Branches ON tblPayroll_Allottees.BranchCode = tblAdmin_Branches.BranchCode INNER JOIN tblAdmin_Banks ON tblAdmin_Branches.BankCode = tblAdmin_Banks.BankCode ON tblData_Allottees.IDAllottee = tblPayroll_Allottees.AllotteeID ON tblPayTypeRefAllot.PayTypeID = tblPayroll_Allottees.PaymentType RIGHT OUTER JOIN tblAdmin_Principals INNER JOIN tblAdmin_Vessels ON tblAdmin_Principals.PrincipalCode = tblAdmin_Vessels.PrincipalCode INNER JOIN tbl_SeamanMain INNER JOIN tblPayroll_Details ON tbl_SeamanMain.SeamanID = tblPayroll_Details.SeamanID INNER JOIN tblAdmin_Positions ON tbl_SeamanMain.PositionCode = tblAdmin_Positions.PositionCode ON tblAdmin_Vessels.VesselCode = tblPayroll_Details.Vesselcode ON tblPayroll_Allottees.PayrollID = tblPayroll_Details.PayrollID AND tblPayroll_Allottees.SeamanID = tblPayroll_Details.SeamanID |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
chriztoph
Posting Yak Master
184 Posts |
Posted - 2010-03-18 : 00:29:55
|
the first query that i posted was a view and here is the WHERE clause in ther Stored PRocWHERE (tblPayroll_Allottees.PayPeriod = @Pperiod) AND (tblPayroll_Details.Included = @IncludedVal ) AND (tblAdmin_Positions.Officer = @OfficerTag1 OR tblAdmin_Positions.Officer = @OfficerTag2) AND --(tblPayroll_Allottees.PaymentType <> 3) AND (tblPayroll_VesselLog.VesselCode = @Vcode) AND (tblPayroll_VesselLog.Closed = @ClosedVal) and (tblPayroll_VesselLog.PayrollType = @Ptype ) and tblPayroll_Details.BatchNum = @BatchNum |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
chriztoph
Posting Yak Master
184 Posts |
Posted - 2010-03-18 : 00:38:22
|
| i think they're not. How to know if they are Indexed or Not? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
chriztoph
Posting Yak Master
184 Posts |
Posted - 2010-03-18 : 00:52:52
|
| All are Indexed but just the Primary keys and those foreign Keys are not.what's the difference between Clustered and Non-Clustered?Is that if Non-Clustered is the with IsIdentity(1,1) and the Clustered is the one that is not inceremental value?is it ok to Index an ID even it is not a Primary Key or Foreign Key? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-18 : 00:55:51
|
| you can index anything you want. but indexes should be on the fields in your WHERE clause and JOIN conditions.by the way, a primary key is an index |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-18 : 01:03:02
|
quote:
Originally posted by chriztoph
All are Indexed but just the Primary keys and those foreign Keys are not.
Having just a PK is not good enough.quote:
Originally posted by chriztophwhat's the difference between Clustered and Non-Clustered?Is that if Non-Clustered is the with IsIdentity(1,1) and the Clustered is the one that is not inceremental value?
No. Clustered means the table is physically stored in that order. Non-clustered means the ordering is logical. You should read up on them though in BOL and other SQL Server sites to become more familiar with them. Indexing is the most important thing for the performance of queries.quote:
Originally posted by chriztophis it ok to Index an ID even it is not a Primary Key or Foreign Key?
Yes. You can index anything you want. An index can contain more than one column too. Don't just add tons of indexes though as that'll negatively impact your write queries (INSERT/UPDATE/DELETE). Do some reading on indexes to get more familiar. But to help solve your current problem, add non-clustered indexes to your foreign keys.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
chriztoph
Posting Yak Master
184 Posts |
Posted - 2010-03-18 : 01:07:44
|
| thanks a lot. i'll do that. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-18 : 01:09:41
|
| Let us know what happens. We'll probably need to suggest indexes to support your WHERE clause, but you should see improvement by adding the indexes to the foreign keys.With proper indexes, good hardware, good database design, efficient queries, and database maintenance, queries should take less than a second to complete in almost all cases (reports would be an exception for instance). We are able to return most of our queries in under 300 milliseconds on a database that is about 1 terabyte in size.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
chriztoph
Posting Yak Master
184 Posts |
Posted - 2010-03-18 : 01:14:31
|
| wow..that fast?ok,i'll let you know what will happen after adding indexes to the foreign keys.is there a T-sql code to add index to to all of the foreign keys in all tables? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-18 : 01:24:36
|
no, you'll have to do that.select * from sys.foreign_keys will give you a head start though  |
|
|
|
chriztoph
Posting Yak Master
184 Posts |
Posted - 2010-03-18 : 02:59:47
|
| In the T-SQL:579974 rows in 3:47 mins faster than before 8:40 mins..Without the where clause 'cause it was the other programmer who did the query 'cause i don't know what to pass on the parameters,'cause it was the other programmer who did the queryBUT in Design View i got Timeout Expired.. |
|
|
|
Next Page
|
Sql Server Memory used