Friday, March 8, 2013

[SQL Server] Performance Issue - Memory

[SQL Server] Performance Issue - Memory

Performance Issue - Memory

Posted: 07 Mar 2013 08:33 PM PST

Hi AllOn one of my SQL Servers, I am noticing what I think is memory pressurePLE is +-100Buffer Cache Hit Ratio is 99%I would think that with a low PLE comes a low Buffer Cache Hit Ratio??I am noticing PAGEIOLATCH waits on top of my wait_stats list - I'm assuming that this ties into the low PLE.Server: SQL 2008 ENT on Windows 2008 ENTMemory: Server memory - 50GB, Memory allocated to SQL - 42GBLocked pages in memory is setI have had some user complaints about performanceWhat is the recommended method to approach thisThanks

Using multiple variables in a Query / Stored Procedure

Posted: 08 Mar 2013 02:15 AM PST

Hello all,I've looked online and have found numerous articles pertaining to my issue here, I'm not exactly sure that it correlates perfectly, however.The below query is functional and works properly, however I want to be able to enter 'BUY,SELL' for @TC and have it output the data for both BUY and SELL with regards to column 'TransCode'when @TC = BUY it will spit out the proper data when TransCode = Buy when @TC = SELL it will also spit out the proper data when TransCode = SELLHowever, I'm looking for the ability to combine both 'BUY,SELL' for @TC and have it return both BUY and SELL.Essentially I'm looking for the ability to eventually have my WHERE clause read:WHERE @TC in ('BUY','SELL','CONTRIBUTION') and have it return data when multiples are selected There are many other variables in the TransCode field other than BUY and SELL (HOLD, CONTRIBUTION, ON ACCOUNT etc..) so bringing back the entire column is not an option... any ideas?declare @idnum varcharselect @idnum = 1declare @TC varchar(50)select @TC = 'BUY'IF @TC = 'ALL'BEGINSELECT @TC as TC, @idnum as IDNum, B.GroupDetail, C.SecID, C.TransCode, C.Quantity, B.GroupId, C.PortfolioIDFROM ERTutTransactions CINNER JOIN FinalGroupDetail BON C.PortfolioID = B.PortfolioIDAND B.GroupId = 1ORDER BY SecIDEND ELSESELECT @TC as TC, @idnum as IDNum, B.GroupDetail, C.SecID, C.TransCode, C.Quantity, B.GroupId, C.PortfolioIDFROM ERTutTransactions CINNER JOIN FinalGroupDetail BON C.PortfolioID = B.PortfolioIDAND B.GroupId = 1WHERE TransCode = @TCORDER BY SecIDEND

Had a job failure but it doesn't appear in the job history

Posted: 08 Mar 2013 01:36 AM PST

Hello,I am documenting job failures in the last two months and I cannot find the one that we had in that period. I looked in the job history and also in the event logs. Can a job fail without recording it? Is there something I have to set to make it write to the job history (I've never known that to be the case)? Thank you in advance.

Update multiple columns in multiple rows

Posted: 07 Mar 2013 07:54 PM PST

I have a table where I need to update multiple columns in each record (multiple rows) to match a single record already in the table.For example:[code]Col 1 Col 2 Col 3 Col 410000 20.00 1 010001 0 0 110002 15.00 1 010003 0 0 0[/code]So, for the above I would like to update values in Col 2, Col 3, Col 4 for records in (10001, 10002, 10003) to match values in Col 2, Col 3, Col 4 where Col 1 = 10000I would like the data to end up like:[code]Col 1 Col 2 Col 3 Col 410000 20.00 1 010001 20.00 1 010002 20.00 1 010003 20.00 1 0[/code]Is it possible to do this in a single statement?

Business Intelligence SQL Server 2008 where to start?

Posted: 07 Mar 2013 04:57 PM PST

Hi guys,Can you recommend a site or a book where I can learn BI for sql server 2008. I know there are tons of books out there by searching google. But I want your recommendations which could save me lots of learning time. regards,JM

Delete trigger with parameters from webform

Posted: 07 Mar 2013 10:56 PM PST

I have a web form with a gridview of a sql table that will allow the user to delete a row. This is a supervisory task that is necessary since updating the table would be a logistical nightmare. I would like to use a delete trigger within SQL Server 2008 R2 that will move the information into a delete history table before it deletes the row. The question that I have is I want to pass 3 parameters to the history table at the same time. Also, in the delete triggers how do I identify fields that need to be inserted into the history table and how do I identify the parameters. One parameter comes from a drop down list on the form. The other two are current date and time which can be calculated either in the trigger or on the form. I also need to pass the username which is cached using Windows Authentication on the form. I have the form completed and the trigger started, but I am getting stuck on the insert syntax especially relating to the parameters.

Worktables & Hash Tables

Posted: 29 Jan 2013 09:14 PM PST

Hi AllAfter testing and researching - I want to make sure that my understanding is correct regarding Worktables & Hash Tables>Worktables & Hash Tables are 2 different things>Hash tables are only created when there is a Hash Join.>Worktables are created when immediate results need to be stored somewhere>Worktables & Hash Tables are both created in Memory first and spill to tempdb if necessary>A Merge join will not employ a worktable if both inputs are uniquePlease advise if I am on the right track here?Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog