Hi,
I have a table with 188376 rows and the data size = 3012 KB, index size = 5884 KB .
LE threshold max is set to 2000 and LE threshol percent to 20%
I have an index on that table and observed that it is not getting used.
I would like to know whether sql optimizer uses the index based on the cost
of the query plan or does the table scan once the LE thresholdlimit is reached
overriding the optimized plan.
thanks,
MMS
I have some code I build 2 weeks ago which I’ve been running daily but it’s suddenly stopped working with the following error.
“The table "tbl_Intraday_Tmp" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit” When I google this there seems to be a related to tables with vast numbers of columns.
My table tbl_Intraday_tmp is relatively small. It has 7 columns. 1 of varchar(5), 3 of decimal(9,3) and 2 of decimal(18,0). The bit I’m puzzled with is it was working and stopped.
I don’t recall changing anything but I wouldn’t rule that out. I ‘ve inspected the source files and I don’t believe they have changed either.
DECLARE
@FileName varchar(50),
@Path varchar(50),
@SqlCmd varchar(1000)
= '',
@ASXCode varchar(5),
@Offset decimal(18,0),
[code]....
I am using MS SQL server 2008, and i have a table with 350 columns and when i m trying to create one more column its giving error with below message -
Warning: The table XXX has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.
INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
how can i resolve this?
is there any threshold manager in the ms sql server? like in the
transaction log, where you can add a stored proc that
will dump the tran log everytime a threshold is hit.
thnks.
Guys I am really stuck on this one. Any help or suggestions would beappreciated.We have a large table which seemed to just hit some kind of threshold.They query is somewhat responsive when there are NO indexes on thetable. However, when we index email the query takes forever.FACTS- The problem is very "data specific". I can not recreate theproblem using different data.- There is only a problem when I index email on the base table.- The problem goes away when I add "AND b.email IS NOT NULL" to theinner join condition. It does not help when I add the logic to the"WHERE" clause.DDLCREATE TABLE base (bk char(25), email varchar(100))create clustered index icx on base(bk)create index ix_email on base(email)CREATE TABLE filter (bk char(25), email varchar(100))create clustered index icx on filter (bk)create index ix_email on filter (email)QuerySELECT b.bk, b.emailFROM base b WITH(NOLOCK)INNER JOIN filter f ON f.email = b.email--and f.email is not nullData Profile--35120500, 35120491, 14221553SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)FROM base--16796199, 16796192, 14221553SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)FROM baseWHERE email IS NOT NULL--250552, 250552, 250205SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)FROM filter--250208, 250208, 250205SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)FROM filterWHERE email IS NOT NULL
View 18 Replies View RelatedHi allI have a large data set of points situated in 3d space. I have a simpleprimary key and an x, y and z value.What I would like is an efficient method for finding the group ofpoints within a threshold.So far I have tested the following however it is very slow.---------------select *from locations a full outer join locations bon a.ID < b.ID and a.X-b.X<2 and a.Y-b.Y<2 and a.Z-b.Z<2where a.ID is not null and b.ID is not null---------------If anyone knows of a more efficient method to arrive at this results itwould be most appreciated.Thanks in advanceBevan
View 8 Replies View RelatedI am doing workload analysis on SSAS - Tabular (2012), I have perfmon logs captured and want to run through PAL. I am looking out for threshold file for SSAS tabular 2012/2014.
View 2 Replies View RelatedThe below stored procedure is used to create a vertical benchmark line on the X-Axis which has a hour scale. I use the stored procedure to find out which temperature crosses or equals the threshold temperature (340), then plot the vertical benchmark line at the hour the first temperature is equal to or greater than 340 degrees and less than 1000 degrees.
The logic below works if the temperature is equal to or greater than 340 degrees and less than 1000 degrees. THE ISSUE is I have 8 temperatures if they don't cross the threshold of 340 degrees I need to set a default value for my vertical line. In other words if the temperature is 180 and my threshold is 340 then set my vertical line on the highest temperature close to 340.
I tried removing my Where clause (but then it breaks the logic for those temperatures that are equal to or greater than 340). I tried using Case When but this didn't give me what I want either. I tried UNION as well. All giving me results I don't want.
Here is what I am looking for:
This first example is one where there was a temperature that was equal to or greater than the threshold of 340 degrees. This is CORRECT
Code:
first_to_cross_thresholdAgeSampleDateDiffAgeovenStartTimestampAgeovenCompleteTimestamp
2014-02-04 19:24:003.3500002014-02-04 16:02:13.0002014-02-05 05:02:13.000
If 8 temperatures did not equal or cross the threshold then give me the hour of the highest temperature close to the threshold but do not return 0.
For Example:
temp1 92
temp2 108
temp3 0
temp4 284 <<< this is the closest to the threshold so give me the hour when this occurred.
temp5 2192 *Remember I can only count temperatures less than 1000 degrees. Anything above 1000 degrees mean there is nothing in the oven. So it is false/positive.
temp6 102
temp7 0
temp8 12
Code:
first_to_cross_thresholdAgeSampleDateDiffAgeovenStartTimestampAgeovenCompleteTimestamp
2014-02-05 00:30:001.3000002014-02-05 02:00:13.0002014-02-05 02:00:13.000
Code:
CREATE PROCEDURE [dbo].[AgeScoreCardThreshold_JJ_12232013]
-- Add the parameters for the stored procedure here
@LicenseNumber int = NULL,
@Lot varchar(50) = NULL
[code].....
According to BOL you can configure an Alert to notify you when the blocked process threshold has been exceeded:
SQL Server 2005 Books Online
blocked process threshold Option
Use the blocked process threshold option to specify the threshold, in seconds, at which blocked process reports are generated. The threshold can be set from 0 to 86,400. By default, no blocked process reports are produced. This event is not generated for system tasks or for tasks that are waiting on resources that do not generate detectable deadlocks. For more information about deadlock detection, see Detecting and Ending Deadlocks.
You can define an alert to be executed when this event is generated. So for example, you can choose to page the administrator to take appropriate action to handle the blocking situation.
Can someone provide some direction on exactly how this is done? Does it require a Service Broker and queue?
SQL Server 2000 8.00.760 (SP3)I've been working on a test system and the following UDF worked fine.It runs in the "current" database, and references another database onthe same server called 127-SuperQuote.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = [127-SuperQuote].dbo.tblCompany.Address1FROM[Work] INNER JOIN[127-SuperQuote].dbo.tblCompany ON [Work].ClientID =[127-SuperQuote].dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDSo now the system has gone live and it turns out that the live"SuperQuote" database is on a different server.I've linked the server and changed the function as below, but I get anerror both in QA and when checking Syntax in the UDF builder:The number name 'Zen.SuperQuote.dbo.tblCompany' contains more than themaximum number of prefixes. The maximum is 3.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = Zen.SuperQuote.dbo.tblCompany.Address1FROM[Work] INNER JOINZen.SuperQuote.dbo.tblCompany ON [Work].ClientID =Zen.SuperQuote.dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDHow can I get round this? By the way, I've rather simplified thefunction to ease readability. Also, I haven't posted any DDL because Idon't think that's the problem!ThanksEdward
View 2 Replies View RelatedReferencing an article regarding MAXDOP and cost threshold for parallelism from Brent Ozar's website: [URL] .....
We have a 2 physical CPUs that are 4 cores each with hyper threading enabled. When looking through the task manager, under the performance tab, I see 16 CPU threads.We have set the MAXDOP value is set at 4.
Reading further, cost threshold for parallelism setting is recommended at 50 to start with.
Our setting is at the default 5.
I have a table that is made up of the sum of medical, mental health and pharmacy claims. I would like to query that to find instances when the sum of the three claims types are greater than a predetermined threshold.
For example:
Patient 1 Medical = 10,000 (could be 10 records at 1,000 each)
Patient 1 Mental Health = 5,000
Patient 1 Pharmacy = 15,000
Patient 2 Medical = 1,000
Patient 2 Mental Health = 0
Patient 2 Pharmacy = 500
Threshold is 25,000
If I queried the above sample table I would get one record:
Patient 1 30,000 - because 10,000+5,000+15,000 = 30,000 and is greater than the threshold.
I am not sure that a having clause would work though.
What is the best practice in setting a minimum support threshold for market basket analysis? Is there a formula? Does it depend on ROI you predict?
View 4 Replies View RelatedI want to configure an alert which notifies me if any of my database log files size exceeds a certain threshold percentage.
View 6 Replies View RelatedI am trying to script a case when to achieve the following.
I have a table of measures with certain threshold. The threshold direction can either be > or < so I want to create a field that shows if the measure hits that threshold or not to be later picked up in SSRS. So a nested case when?
CASE WHEN M.[Threshold Direction] = '>' THEN
CASE WHEN A.[Value] > M.[Threshold] THEN 'GREEN'
CASE WHEN A.[Value] < M.[Threshold] THEN 'RED'
ELSE ''
END END END AS 'Condition'Is this at all possible?
I right in thinking that if the estimated subtree cost is higher than the cost threshold for parallelism then it will use a parallel plan? If so, I've read the cost threshold is measured in minutes but is the subtree cost measured in something else, the mysterious cost number? And if so, how are the two compared?
View 9 Replies View Relatedhi, im trying to get the last row of a column by using this code
cmd.CommandText = "SELECT max(orderID) as orderID from orders where memberNo = '" & Session.Item("memberNum") & "'"but it return me 1 instead which is the smallest value in my orderID column n located in the first rowthen i try to use this code but they say got syntax errorcmd.CommandText = "SELECT max(orderID) as orderID from orders where memberNo = '" & Session.Item("memberNum") & "'" ' order by orderID asc " ' and orderID = '" & Session("ordersID") & "'"
can anyone tell me the right way to write the syntax??
thx in advance
Does anyone know the specific website that documents the maximum RAM SQL 7.0 Standard and Enterprise edition can handle within Windows 2000 Server and Windows 2000 Advanced server? All help appreciated!!
darius
Can anyone tell me the maximum number of (if there is a maximum) for the following :-
1. SQL Server Users per Database
2. Roles per Database
3. Connections per Server
4. Logins per Server
Thanks.
Hi Everybody,
Our development team is doing some stuff in ASP. They are interested only in char and varchar
data types. They clearly told us that they are not going with text and image data types.
Is it possible to make a row size behind 8060 bytes in a table without text and image data types?.
tks in advance,
Sri.
I'm using replication between two server, but I don't know the maximum record will be move from Database 1 on Server1 to Database2 on Server2 in per minute.please help me, thanks!
View 2 Replies View Relatedhow to filter on a maximum value ?
My query is this:
SELECT
ProdspecUID
,itemcode
,model
,revno
FROM prodspec
The revision numbers (revno) are unique per itemcode.I would like to get only the rows with the highest revision per itemcode, but I also need the prodspecUID with is the index key and therefore unique.I have tried MAX() and TOP 1's in sub queries, but up until now without any result.
Hello
Without technical information and DB design, I wish to know the maximum entries in MS SQL in million. I guest for access are a limit of 2 million and a 2GB DB.
How much for MS SQL in max. entries and how much in GB?
A lot of thx for your feedback.
Dominique Javet
hi guys,
I have 4 columns and 3 rows. Columns are Name, Age, Gender and Weight. I have values entered for each column. I need to pick the highest value of weight if Name, Age, Gender are same and put that into new table. how can i do that?
Ex Table
-----------------
ColName====> Field Name
------------
xyz
abc
xyz
abc
abc
sdf
abc
....
....
---------
In table column string. i need maximum ColName.. In above table maximum ColName is abc... also top 3 maximum ColName i need...
Result
------
abc
How to i'll get any query........
Top 3 Maximum Result
--------------------
abc
xyz
sdf
----------------------------
this also.......
Hi...
I have written a stored procedure with number of inserts as the input variables which will input those many rows into my execution table... When i try to input more than 31 rows it gives me a error shown below.... I have a trigger attached to the execution table where i am inserting the rows... I am unable to sort out the error..
If anyone knows whats the problem Please reply.... Thank you....
Msg 217, Level 16, State 1, Procedure runtime_insert_elevator_table, Line 105
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
anup
Dear all,
I have a input table like this:
FieldA FieldB FieldC
------------------------------------------------
ItemA 2007-10-01 10
itemA 2007-10-10 20
ItemA 2007-10-20 30
ItemB 2007-10-05 5
ItemB 2007-10-26 9
Result :
FieldA FieldB FieldC
------------------------------------------------
ItemA 2007-10-20 30
ItemB 2007-10-26 9
How can I get such result? i.e. I want to get the record with the latest date from each item group?
thanks in advance!
I want to select the latest photos that were posted on my site from my photos table:id name usercode createdate1 holiday 5 1/1/20082 holiday2 5 1/1/20083 my car 5 1/1/20084 new home 7 1/1/20085 starry night 8 1/1/20086 me again 6 10/10/2007But in case one user has posted like 400 photos I dont want to show 400 photos of the same user as that would mess up latest photos. So I want to show the latest photos but always with a maximum of 3 for the same user...So in the above data example pictures with id 1,2,3,4,5 would be shownHow can I achieve this with SQL (and as always: without temp tables :))Thanks!
View 7 Replies View Related
hi...i need a stored procedure query to get the maximum occurred value ie(more than one time occurred value).... this is my table structure.... table Name: PHOTO_GALLERY_VISITSpgv_id
pgv_main_category_id pgv_sub_category_id pgv_path
pgv_visitor_id
pgv_visited_date 6 54
72
photo/Writer/Content/Mobiles/LG/camer4.jpg 26
4/3/2008 6:41:02 PM 7 54
73
photo/Writer/Content/Mobiles/Samsung/mobile1.jpg 26
4/3/2008 6:41:43 PM 8 64
85
photo/Writer/Content/Movie/Bachan/bhachan1.jpg 25
4/4/2008 3:37:17 PM 9 63
82
photo/Writer/Content/sports/Cricket/cricket1.jpg 34
4/4/2008 3:37:29 PM 10
54 73
photo/Writer/Content/Mobiles/Samsung/mobile1.jpg 34
4/4/2008 3:37:29 PM 11
54 73
photo/Writer/Content/Mobiles/Samsung/mobile1.jpg 26
4/4/2008 3:37:29 PM 12
54 73
photo/Writer/Content/Mobiles/Samsung/mobile1.jpg 54
4/4/2008 3:37:29 PM 13
54 72
photo/Writer/Content/Mobiles/LG/camer4.jpg 59
4/4/2008 3:37:17 PM The above is my sample table...... In that filter the most occurrence based on the column "pgv_sub_category_id"in the above table the 73 is the maximum occurred id after that the second maximum ie..72 is the 2nd maximum occurred....hence i want the output as only the id & path which occurred more than one time..... pgv_sub_category_id pgv_path 73 photo/Writer/Content/Mobiles/Samsung/mobile1.jpg 72 photo/Writer/Content/Mobiles/LG/camer4.jpg plz anybody send me the query....its urgent.....thanks in advance.....regardsjanu
Hallo Cracks,
what I try is a little bit heavy, maybe, but I only miss the minimum/maximum fuction - or I didn´t found it; not here in the Forum and also not in the onlinehelp of the SQL Server.
What I try to do:
I have 2 columns in my table; a start- and an end-date. For this period of time between end and start i have to calculate the days for the years. Here my thoughts (for the current year):
Is the startdate <= 31.12.2004 and the enddate >= 1.1.2004 i have to calculate die datediff between max(1.1.2004/startdate) and min(31.12.2004/enddate)
like this sqlstatement:
SELECT CASE WHEN dbo.Phases.phasenstart <= CAST(CAST(YEAR(GETDATE()) AS
varchar) + '-31-12' AS smalldatetime) AND dbo.Phases.phasenabschlussist >=
CAST(CAST(YEAR(GETDATE()) AS varchar) + '-01-01' AS smalldatetime)
THEN 365 ELSE 0 END AS Expr2,
FROM dbo.Phases
WHERE (phasenstart IS NOT NULL) AND (phasenabschlussist IS NOT NULL)
instead of 365 there must be the above calculation. Is start=3.1.2003 and end=30.1.2004 I expect as result only the 30 days in 2004.
thanks in advance and kind regards :-)
Cappu
Is there a way to alter the default 256 value to some other value in a transac SQL script?
The value can be changed manually in the advanced tab of the current connection options. I need to be able to set and reset this value in a script and/or stored procedure.
Kind of urgent for an answer to this problem.
Thanks in advance.
In SQL 7, what is the Maximum # of databases per Server?
thanks in advance.
Hi.
How many separate databases can one SQL Server (7.0) manage?
Thanks in advance
Dan