Query To Show Life Cycle Revenue?

Jan 2, 2007

I am trying to create a query that will show how much revenue that we have recieved from a customer After the first invoice and I'm having a difficult time creating a query to do it.. I have a customer table  and a sales table joined by custno.

SELECT     Customer.LastName, Sales.InvDate, Sales.AmtCharge
FROM         Customer INNER JOIN
                      Sales ON Customer.CustNo = Sales.CustNo

 The output I'd like is

CustNo, LastName, FirstInvoiceAmount, LifeCycleAmount

Getting the first inv date seems straight forward

SELECT Customer.CustNo, MIN(Sales.InvDate) AS FirstInv FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo GROUP BY Customer.CustNo

However getting the amount of that first inv and then getting the sum of all invoices not including the first invoice has me scratching my head.

 Can anyone point me in the right direction?

 

View 2 Replies


ADVERTISEMENT

Show Monthly,Quarterly, And YTD Revenue

Dec 7, 2006

Hi All Experts,

I need to show a result of a revenues total for Monthly,Quarterly, and YTD of a given sales person by a given period.

e.g

Period : 7-1-2006 to 8-1-2006

Sales Person Monthy Quarter YTD

SalesPerson1 $1999 $10000 $100000

SalesPerson2 $1999 $10000 $100000

How can i do that?

Thanks in advance.

View 7 Replies View Related

Query Brain Teaser - Revenue Projections

Mar 8, 2006

I have a requirement (motivated by a SOX thing) that is just giving mefits. I know it should be easy and I'm probably overthinking it, but Ijust can seem to find the best way to get where I need to go.I have some payment projection data derived from a huge procedure thatI'm dumping into a temp table that looks like looks this:Key Pd Start End AnnualAmt MonthAmt DailyAmt6789 1 2005-06-01 2010-05-31 49,500.00 4,125.00 135.6164386789 2 2010-06-01 2015-05-31 54,450.00 4,537.50 149.1780826789 3 2015-06-01 2020-05-31 59,895.00 4,991.25 164.0958906789 4 2020-06-01 2024-05-31 65,884.50 5,490.38 180.505479(there are actually 6 levels of keys, but you get the idea)I need it to get into a reporting table looking like this:Key Rev Year ProjectedAmt6789 2005 29,021.926789 2006 49,500.006789 2007 49,500.006789 2008 49,500.006789 2009 49,500.006789 2010 20,478.086789 2010 31,924.116789 2011 54,450.006789 2012 54,450.006789 2013 54,450.006789 2014 54,450.006789 2015 22,525.886789 2015 35,117.406789 2016 59,895.006789 2017 59,895.006789 2018 59,895.006789 2019 59,895.006789 2020 24.779.10etc...I'm having a problem wrapping my head around how to get the rows in themiddle of each period.The other, probably minor and statistically insignificant, issue isproration on a leap year. If a proration occurs on a leap year and Ihave to calculate the proration based on a DATEDIFF and an Annual orMonthly Amount, I'm going to be a day over.Anybody have any tricks or ideas???Thanks so much for your help!Jody

View 3 Replies View Related

SQL 2012 :: Buffer Cache And PLE (Page Life Expectancy) Query

Aug 17, 2015

I'm getting an alert which states that both my Buffer Cache Hit Ratio and PLE are low on one of my SQL Servers though I'm not sure how to correctly check this.

I ran:

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Buffer cache hit ratio'

Which gives me the Buffer Cache Hit Ratio, cntr_Value of 9 though its constantly dipping between 3-3000 and is never steady and I'm unsure if this is normal.

I also ran:

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Page life expectancy'

Which gives me the Page life expectancy of 209061.

If these values would cause concern and if this is a normal Buffer Cache Hit Ratio? It's constantly dropping from high or low from what I can see. These scripts were pulled from another forum and I'm assuming they're showing the correct values.

View 1 Replies View Related

Revenue By Month

Mar 31, 2008

Hi everybody!

Is there any way that I could achieve displaying the revenue grouped by a month?

I've got the following fields in order table:
DateClosed,
StartDate,
EndDate,
OrderValue

The fiscal year starts in march and ends in feb.
I know how to calculate the year's revenue but I have got difficulties in spreading it over the months.

Any thoughts?

Thanks in advance.

View 4 Replies View Related

SQL 2012 :: Finding Less Than 10% Revenue Accounts In A Table?

Mar 18, 2015

I'm trying to find out less than 10% in revenue accounts from a table. Below is a snapshot. Basically, I want to add Revenue mix column in the table using procedure.

ACCOUTSREVENUEREVENUEMIX
ACCOUNT1 100 2%
ACCOUNT2 200 4%
ACCOUNT3 500 9%
ACCOUNT4 1000 19%
ACCOUNT5 1500 28%
ACCOUNT6 2000 38%
TOTAL 5300 100%

View 1 Replies View Related

Error Log Cycle

Mar 10, 2005

Quick question (I hope) regarding the cycling of the sql server error logs.

I am looking at implementing a daily / weekly job to run the stored procedure "sp_cycle_errorlog", but was wondering if there was any way of stopping the sql server process cycling the logs automatically when it is started? Therefore the logs will only be cycled when the job runs.

Many thanks.

View 1 Replies View Related

Counting Records Using A WHILE Cycle

May 27, 2015

I have a table that contains a list of departments (about 50) and another table that contains helpdesk tickets, each record storing the ID of the department sending the ticket. So I'm trying to count how manay tickets per department, I tought of using a subquery and a WHILE cycle but Its just not happening..I sketch up this query:

Code:
WHILE (SELECT MAX(DepartmentID) AS c FROM dbo.tblDepartment) i < i.c
BEGIN
SELECT COUNT(DepartmentID) AS DepartmentCount
FROM dbo.tblTask
WHERE (DepartmentID = @Variable)
END

how could I build this query or what better way of doing the job there are...

View 1 Replies View Related

How Can You Stop And Cycle Through A Column(s) Of

Jun 16, 2007

Ok, at this point I have the reader reading the tables data in a loop while it's not empty. During the gathering of each row of data, I was wondering if it was possible to do a next row once I've reached a certain column. The main users table has just the one user, but it's relationship table has a couple family members. I was hoping someone could show me how to make it so that the one user and all his related family members will print out to a label.
while (reader.Read())
{
string usr = reader["UserName"].ToString();
usr = usr.TrimEnd();
string pss = reader["Password"].ToString();
pss = pss.TrimEnd();

if (usrNmeLbl.Text == usr)
{
if (psswrdLbl.Text == pss)
{
//read the column from the reader and cast it to String as some may contain null values
usrNmeLbl.Text = reader["FirstName"].ToString() + " ";
psswrdLbl.Text = reader["LastName"].ToString() + "<br />";
psswrdLbl.Text += "Place of Birth: " + reader["BirthPlace"].ToString() + "<br />";
psswrdLbl.Text += "<img src=" + reader["Photo"].ToString() + " />" + "<br />";
Label4.Text = "Your relatives: " + "<br />";
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
}
If I grab the Relation table data again, it's not cycled to the next relative. I was hoping that it would, but it's not. So I'm wondering if there was something that could be added to the second set.
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();

Thank you in advance.

View 2 Replies View Related

52 Week Backup Cycle...

Sep 13, 2007

Hi

uh

im on the blag....

Can someone help me with a script for the following?

7am tuesday, week 1 of the year:
full backup, Trans log backups from between 8am and 10pm and nightly diff @ 10.15pm. both TL backups and Diff backups append to the full file. the next day - weds - exactly the same only without the initialising full backup @ 7am. each day appends to the same weeks backup. this goes all the way through to 10.15pm Monday night which is the last activity on the particular backup file.

7am Tuesday, week 2 of the year:
exactly the same process but writing to a new backup file. backup file is named something useful, like with the date of the Tuesday for example..

ill manually delete things as I need to (once that months data has been written to End-Of-Month tape), but otherwise i'd ideally end up with 52 backup files, each containing a weeks worth of data.

help me obi-wan SQLee. cos I cant bloody do it....

Thanks

Alastair
Methodology



"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.

View 6 Replies View Related

Power Pivot :: Revenue Schedules - Creating Rows By Number Of Months

May 26, 2015

I am trying to calculate how much revenue we may get, based on potential new business opportunities. The core fields we have are

Total Contract Value ($ or £)Duration of contract (months)Revenue start dateVarious information about the new business - ID, Title, Customer etc.

We can easily calculate the revenue per month with "Total Contract value divide by duration".

However what I would really like to do is be able to know how much revenue we will be getting each month.

To do this I was thinking we should probably create a new row for each month entry, with the mm-yyyy being the only difference for each row. But how to create the appropriate months and the correct amount of rows.

View 3 Replies View Related

Query Works In 'test Query' But Refuses To Show Up In The Datagrid On A Web Page - Urgent!

Mar 28, 2007

Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
 SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC

 Here is the page source
 
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="&#9;SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches&#13;&#10;&#9;FROM dbo.MAKES INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID&#13;&#10;&#9;WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )&#13;&#10;&#9;GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID&#13;&#10;&#9;HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2&#13;&#10;&#9;ORDER BY count(*) DESC&#13;&#10;">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
 AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
 Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
 Please help!
 
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
 

View 4 Replies View Related

How Can You Stop And Cycle Through A Column(s) Of Data?

Jun 16, 2007

Ok, at this point I have the reader reading the tables data in a loop while it's not empty. During the gathering of each row of data, I was wondering if it was possible to do a next row once I've reached a certain column. The main users table has just the one user, but it's relationship table has a couple family members. I was hoping someone could show me how to make it so that the one user and all his related family members will print out to a label.
while (reader.Read())
{
string usr = reader["UserName"].ToString();
usr = usr.TrimEnd();
string pss = reader["Password"].ToString();
pss = pss.TrimEnd();

if (usrNmeLbl.Text == usr)
{
if (psswrdLbl.Text == pss)
{
//read the column from the reader and cast it to String as some may contain null values
usrNmeLbl.Text = reader["FirstName"].ToString() + " ";
psswrdLbl.Text = reader["LastName"].ToString() + "<br />";
psswrdLbl.Text += "Place of Birth: " + reader["BirthPlace"].ToString() + "<br />";
psswrdLbl.Text += "<img src=" + reader["Photo"].ToString() + " />" + "<br />";
Label4.Text = "Your relatives: " + "<br />";
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
}
If I grab the Relation table data again, it's not cycled to the next relative. I was hoping that it would, but it's not. So I'm wondering if there was something that could be added to the second set.
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();

Thank you in advance.

View 3 Replies View Related

SQLAgent Doesn't Cycle Log Gracefully

Jan 26, 2007

Scenario: SQL Agent job calls "EXEC msdb.dbo.sp_cycle_agent_errorlog" once a week to cycle the SQL Server Agent log. Job is owned by "sa".

Most servers run this with no problem, but one active node of an active/active SQL Servers cluster fails with the message:

Executed as user: DOMAINSQL_Service. SQLServerAgent Error: The process cannot access the file because it is being used by another process. [SQLSTATE 42000] (Error 22022). The step failed.

The SQLAgent job actually appears to be doing its job... a new SQLAgent.OUT is generated with the event:

(Date/Time)+ [412] Errorlog has been reinitialized. See previous log for older entries.

If I try renaming the file SQLAGENT.OUT, I get the message "It is being used by another person or program," which I expect. If I stop the SQLAgent service, I can rename the file with no problem. Failing over has no effect.

I just don't understand why this job fails for this server. (It should be mentioned the job could be fixed to "Succeed on Failure," but I'd rather not.)

View 14 Replies View Related

Help Problem On SELECT Cycle Backward

May 29, 2008

need help on logical SELECT cycle



Code Snippet
SELECT empID, unit_date, unit, ISNULL(NULLIF ((unit + DATEDIFF(mm, unit_date, GETDATE())) % 4, 0), 4) AS new_unit
FROM dbo.empList




i have this code
this code change the value field "new_unit" evry month from 1 > 2 > 3 > 4
like this evry 4 month it return to 1 >2..........
------------------------------------------

if i put
unit_date = 01/05/2008
and unit=1
than new_unit=1
IT OK

but

if i put
unit_date = 01/04/2008
and unit=1
than i get new_unit=2

but it should be 3
it dont go backward ok



if i put
unit_date = 01/06/2008
and unit=1
than i get new_unit=4

but it should be 2
it dont go forward ok !

TNX

View 1 Replies View Related

Display Flag For OrderType Cycle Values

Aug 9, 2015

I have a table with EquipmentNumber, OrderType, BreakdownIndicator and RowID columns and below are the values for one equipmentnumber,

EuipmentNumber OrderType BreakdownIndicator RowId Flag
145812 PM04 1 Yes
145812 PM07 2 Yes
145812 PM07 3 Yes
145812 PM04 4 Yes
145812 PM07 5
145812 PM07 X 6
  145812 PM07 7
  145812 PM04 8 Yes
145812 PM04 9 Yes
145812 PM04 10 Yes

I need to display the 'Flag' column in the result set as mentioned above to identify the cycle values without breakdown indicator values. Here is the logic for this,

1. Need to set the flag 'Yes' for the cycle values PM04 to PM04 in the OrderType and if there is any breakdownindicator value 'X' then we can not consider that cycle for Flag.

View 12 Replies View Related

Transact SQL :: Get Start And End Date Of Billing Cycle

Oct 9, 2015

I have weekly billing cycle. And the billing cycle can start on any day of the week.

Assumption: Start day of week is MONDAY

Hence, the following

DayNumber Day
1 Monday
if start day is 1, then billing cycle is Monday - Sunday
2 Tuesday
if start day is 2, then billing cycle is Tuesday - Monday
3 Wednesday
if start day is 3, then billing cycle is Wednesday - Tuesday
4 Thursday
if start day is 4, then billing cycle is Thursday - Wednesday
5 Friday
if start day is 5, then billing cycle is Friday - Thursday
6 Saturday
if start day is 6, then billing cycle is Saturday - Friday
7 Sunday
if start day is 7, then billing cycle is Sunday - Saturday

For a given date, i need find start date and end date of current billing cycle.

For example, if my billing cycle starts on 3rd day of week (wednesday), then for the input 09-Oct-2015, i need to get two output 07-Oct-2015 and 13-Oct-2015

View 6 Replies View Related

Size Of Log Life

Dec 3, 2007

I am using SQL 2005. I have one very big database. The size of the DB is 1500986.88 MB and space available is 149359.48 MB. There are log files in this DB. The size of my log files are growing so big. The currently allocated space of one log file is 799874.50 MB and available free space is 38281.67 MB (4%). And the currently allocated space of another log file is 1500 MB and available free space for this log file is -760092.83 MB (-50672%)

I am not sure how come available free space for the 2nd log file becomes –ve. What does it mean? If I shrink log file, will I get any extra space? If not, what is the solution?

View 4 Replies View Related

SQL 2005 Screwed My Life

Sep 14, 2007

My one VB Exe used to connect SQL 2000 using windows SQL Server Driver. But same exe is giving problem of SQL Connection Timed out error in SQL 2005 - SP1.
As this exe refers 5 tables from database and insert as well as update the data. Also client pc and server pc's ping test is around 30 to 40 ms.
Is there any way where we can increase timed out level at SQL 2005.

One more thing which i noticed in 2005. its really very heavy software. even my IBM Xeon_346 server 3GB Dual CPU, 3 GB RAM also not able to handle.

Thanks in Advance
Dino


Dina Satam

View 14 Replies View Related

Page Life Expectancy=0

Feb 28, 2006

In my MSSQL server 2000 sp4, in performanece monito, buffer memory, pagelife expectancy is equal to 0.00000 (average for 10 sec, auto update). Ithink sth is wrong configured, but what?Marek

View 1 Replies View Related

Get The Most Recent In A Group (was: For The Life Of Me I Can't Write ...)

Jul 3, 2007

Hi. I have a table called "Maxes" with three fields: Exercise_ID, weight, and date. This is for journaling my weightlifting progress. What I want my query to do is this:
Return just one record for each Exercise_ID, and only the one with the most recent date.

I tried this:

Code:

SELECT DISTINCT Maxes.Exercise_ID, Maxes.Date_Maxed, Maxes.[Max Weight]
FROM Maxes ORDER BY Maxes.Date_Maxed DESC;

but it doesn't quite work. Can someone show me how to do this?

View 4 Replies View Related

SQL 2012 :: Page Life Expectancy Is Too Low?

Feb 18, 2014

I started receiving these alert messages, and after doing some re-search still can't figure out how to totally resolve it. From what I gather the value Microsoft stipulates 300 for PLE is not accurate if you running a 64 bit OS and dependent of the amount of RAM you allocate to SQL.

If I allocate 20 Gig of RAM to SQL, The PLE should not drop below 1500 - (PLE should be 300 for every 4 GB of RAM) (20/4)*300

During the course of the day it sometimes drops below 1500, so my question is how can I further see why and what query is causing this to happen???

I setup a monitoring job as mentioned by Steve Hood to capture results for me every 20 min.

View 6 Replies View Related

Page Life Expectancy Issue

Mar 17, 2008

STUCK! I have a consistent PLE value of 0. The server has 8GB RAM, I/O-CPU counters under 30%, various DMV's results show the buffer and memory cache do not calculate up to it's AWE (32-bit) allocation 7.5GB, waits don't show any problems, no locks, no blocks.

There are improvements to indexes that have to be made but where can I identify the root cause to the low PLE? The same process runs x6 quicker on 2 other much lower spec SQL servers exact copies from a data prospective, however there PLE is very high and not production.

Any ideas?

View 1 Replies View Related

Page Life Expectancy Is POOR

Jan 16, 2008

Page Life Expectancy (PLE) is pretty bad on my server. PLE is hovering around 3 minutes "sometimes" but is usally around 20-30 seconds.

Total memory allocated to SQL ( a fixed amount ) is set at 3GB.

Of the total memory allocated, SQL Server is using 2.52GB ... so there is room if needed.

The Buffer Cache is sitting at 2.09GB with a hit rate around 99.8%.

The Procedure Cache is sitting at 378MB with a hit rate around 90.5%.


CPU is hovering around 10-20%


Free System Page Table Entries is LOW ... at 22343

Disc Queue Lengths spike quite often to above 5 and sometimes as high as 36. Usually sitting at .05 to 1.0 (and there are times when the DQLs are great and not measureable.

What I need to find out is how to get PLE above the recommended 5 minute mark???

Please let me know if there are any other items I need to note.

Thanks!

=========================================
Here are some hardware/Software/Implementation stats:
=========================================

SQL Server 2005 Standard w/ latest patch of 3152

Windows Sever 2003 R2 Standard w/ latest patches applied (says PAE is enabled in the System Properties.. General tab

4 Intel Xeon X5355 @266GHz

4GB RAM with 3GB dedicated to SQL Server via the /3GB switch


System Disc ( C: ) is 136GB (free space is 122GB) and is RAID10
Data/SQL Disc ( G: ) is 408GB (free space is 347GB) and is RAID10
The SQL files (MDFs/LDFs, TempDB, DB & TLog backups, SQL application and all that is SQL related) are all on the single array (G (** which I must note is NOT how I configured the SQL environment but aquired the setup when I started the position).

View 6 Replies View Related

Query To Show ID That Is Not In DB?

Oct 24, 2013

I have a list of OrderIDs and I'd like SQL to show me the ones that do not appear in the DB.

Lets say that my table looks like this:
idorderIdUserItem
122joepotatoes
223SteveApples
324SteveBananas
438DaveCarrots

And the query looks like this:
SELECT orderID FROM sales WHERE orderID IN ('22','51','38')

You'll notice that orderID 51 is not in the table. How do I make it tell me the orderIDs that do not appear?

View 1 Replies View Related

SQL SERVER 2000 End Of Life ? Support Issue.

Aug 2, 2007

Hello,

This is my first foray into SQL Server and i am coming from an Oracle background.

We are currently looking to upgrade a COTS package from a third party supplier. I would like to know is there any end of life announcement for SQL Server 2000 ? Is there an End of Support date announced for SQL Server 2000 ?

In Oracle there is a product life cycle announcement screen, I would like to know if there is a similar page in SQL SERVER and where ?

thanks


View 3 Replies View Related

DB Engine :: Page Life Expectancy Above 100K

May 27, 2015

I have few servers that are VM and different version of SQL Severs (2008 Express/Standard, 2012, or 2014 Enterprise) and seems all of them have same issue that when I am querying "sys.dm_os_performance_counters" for "[object_name] LIKE '%Buffer Manager%' AND [counter_name] = 'Page life expectancy'" I am getting very big number (Ex, above 100K or some servers 1M).Is this number seems fine or acceptable? 

View 6 Replies View Related

How To Show Boolian Value In Sql Query?

Apr 1, 2008

i have one table completed tabletask_id   task_due_datetime   task_completed_on  1                    03/21/2008        03/25/20082                    03/12/2008        03/10/20083                    03/10/2008        03/18/2008 i want the output:task_id    on time        late 1                  0             1 2                  0             0 3                  0             1  how i get this output in sql query without the use of cursor...  

View 5 Replies View Related

Query To Show Duplicates

Aug 16, 2005

mytable      fld1 int     primkey                  fld2 varchar(20),                  fld3 varchar(20)From the definition of the above table, how do i do i modify my below query to only select the rows with duplicates in fld2.  I know a groupby with a having count will display the duplicates for a given field, but i want my query to see all the fields and rows that are duplicates.     select fld1, fld2, fld3 from mytable                

View 1 Replies View Related

SHOW Query For MSSQL

Sep 18, 2004

What would the MySQL equivalent for "SHOW TABLES" and "SHOW FIELDS" be in MSSQL?

View 8 Replies View Related

Query To Show All Data According To ID

Feb 1, 2015

I have a table i need to show data according to id. distinct id and all name should be comma separated against each id.

DECLARE @TBL TABLE (ID NUMERIC(10), NAME VARCHAR(10))

INSERT INTO @TBL (ID,NAME)
VALUES(1, 'A'), (1,'B') ,(1,'C') ,(2,'E') ,(2,'B') , (3,'F') , (3,'G')

output :

id name

1 a,b,c
2 e,b
3 f,g

View 2 Replies View Related

Show Me Example Of Dynamic SQL Query

Mar 5, 2008

Show me example of dynamic SQL query if possible

thank you

View 5 Replies View Related

Setup And Upgrade :: Page Life Expectancy Less Than 300 Seconds

Jun 9, 2015

Every day, at same hour, my SQL Server is showing error of PLE less than 300 seconds.

So after this, the server works properly.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved