Index Granularity
Oct 4, 2006Hi,
I was looking at the new index locking granularity option available in 2005. I did not understand in what case can this be a performance enhancement. Has anybody looked into this ?
ADVERTISEMENT
Time Dimension According To Different Granularity
May 29, 2008Hi,
I got inventory fact table. For the past two weeks, I got on a daily level; beyond that, weekly level, and beyond that monthly. I need to tie it all in to the time dimension of course €“ and the problem is, how do I do it on different granularity?
As far as time dimension, tn the datamart, I got tables dim_date with key column date_id (int) , and correspondingly dim_week with week_id(int) and dim_month with month_id(int).
What I€™ve done so far, is created a time dimension from dim_date table (meaning granularity=daily) and simply tied in all the inventory €“ daily, weekly, monthly, on the day level (it all has date_id field in it, even the weekly and the monthly. Its simply the day of the end of week or end of month) I didn€™t tie anything to dim_week and dim_month.
Does that makes sense? The result is kind of strange. I cant upload an image here, but€¦ well it seems ok, I got year, week (€˜GL Week€™) and then €¦ this is the annoying thing: why am I getting a €˜date€™ column, when I only want it by week or by month? I can€™t make that column disappear (e.g when in time hierarchy I only group by month, still a €˜day€™ column will be there, and will show 4 days€¦ the 4 €˜end of week€™ that comes from €˜date_id€™.)
How do I make it go away??
Thanks for reading : ) I know it€™s a bit long
Sum At A Lower Level Of Granularity
May 19, 2008Hi all,
I have a big problem with the design and the queries of a couple of tables. I have to calculate the weighted average of the number of days between an invoice and the sum of its payments (rateal payments), weighting this number with the invoice's amount, and only when the sum of the invoice's payments equal invoice amount.
I have designed two FactTables to accomplish this:
Example:
data in the fist table (FactInvoice) looks like this:
CustomerId, InvoiceDate, InvoiceNumber, InvoiceAmount
1234567 2008-03-10 123 1000.00
1234567 2008-04-10 150 2000.00
and data in the second one (FactPayments) looks as below:
CustomerId, PaymentDate, InvoiceNumber, PaymentsAmount
1234567 2008-03-10 123 500
1234567 2008-03-15 123 500
1234567 2008-04-20 150 800
1234567 2008-04-23 150 1200
Let's suppose that I'm querying the cube for the customer 1234567, at the date 2008-05-01. I need to sum the payments for the first invoice multiplicating it for the count of days elapsed, then divide the number by the InvoiceAmount (or PaymentsAmount, is the same) : (0 days * 500‚¬ + 5 days * 500‚¬ + 10 days * 800‚¬ + 13 days * 1200)/3000‚¬ = 8,7 days of weighted average to cash an invoice.
How can I do this with Analisys Services ? Is it too complicated ? Here, in the southern Italy, the problem of customer's debit is heavily felt, and total like this are really important!
Any help or suggestion will be really appreciated.
Marco
Lock Granularity And Performance Issues
Jun 9, 2008Hi I have a few things to clear up and I hope i can find some answers in here.
I have an application written in C# using COM+ components
This application is intended to support a few hundred users at the same time and each action user leads to a few updates on several tables.
The problem is that 2 or more users might do this thing the same time meaning that each users should update rows that "belong" the other users .
I rely on the appearance of deadlocks in order to keep the data consistent, so only one of the users should be able to have this action completed, the transactions for the other concurrent users should abort.
Basically the results would be the same no matter which user will complete the transaction so the only issue is to have only one action completed. So far so good, it seems that I have no problems and that the data is consistent.
Now the reason why I'm here would be that lately i see that the number of deadlocks has increased considerably and there should be no reason for this to happen; the situations when the users would modify each others data are somehow rare and I should see a few hundred deadlocks daily
So what i think is that SQL might increases the lock granularity to table or page instead of using row locks(I am not sure it's just a wild guess)
From what I've read, sql starts with default row lock and it might increase it when necessary.
And now finally the question: can I force sql to use only row lock? and if yes are there any risks involved? Is sql capable to maintain only this kind of lock and still be able to manage the transactions correctly?
And another question, how do deadlock decrease overall performance?
Budget On Different Granularity In Organization Hierarchy (which Is SCD)
May 27, 2008Hi everyone
I am struggling with adding budget numbers to a cube. The main reason being that the budget is *not* on the finest granularity (employee) with regard to the organization hierarchy but on a coarser one (team).
The organization hierarchy is a "flat" (not parent-child) hierarchy that looks about like that:
employee -> team -> teamgroup -> region -> country
As mentioned I now have budget numbers that are defined on the team-level (not on the employee level as "regular" measures). I would know assume that I could put the budget data into its own table and "link" it with the organization through the "team" attribute. I would do that on the "dimesion usage"-tab.
The problem with this approach is that the organization is changing (SCD type 2). This essentially means that by linking to the "team" attribute the aggregation of the budget data on higher levels of the organization hierarchy can be ambiguous (at least that is what I understand).
Example organization table:
Code Snippet
surrKey busKey empName teamId teamGroupId regionId countryId ... scdStuff
1 1 Raphael 1 1 1 1 ...
2 2 Jeanne 2 2 1 1 ...
3 3 George 3 3 1 1 ...
4 2 Jeanne 2 3 1 1 ...
That would mean that on some point in time team 2 (consisting of one employee, Jeanne) moved from teamgroup 2 to teamgroup 3. Just for the sake of a simple example.
Now, what am I to do with my budget data in this situation? I cannot link it to the teamId, because teamId = 2 for example cannot specify if the value should be aggregated into teamgroup 2 or teamgroup3...
I have a feeling that this got something to do with the design of the organization-table but I am unsure about what the actual problem is. Any hint, pointer or solution would be appreciated. If the question is unclear, please let me know and I will try to clarify.
Kind regards
scherand
Analysis :: What Is The Meaning Of Granularity In SSAS
May 6, 2015I wanted to know the meaning of Granlatiy of Fact with some example.
View 3 Replies View RelatedSecurity Need Thoughts: Ease Of Admin And Granularity
Oct 3, 2005I like the new gig a lot. Real busy, smart folks and I have been in high demand since 5 minutes after my butt hit the chair. I already have code in production.
Anyhow, we have a security situation on the sql servers I pointed out on my first day. So they want me to roll everything over to Windows Authentication and give the developers and report writers more restricted rights inside SQL Server. So they have NT Groups for different kinds of users and all of that jazz and I layed on the typical stuff about using NT groups vs individual accounts and ease of admin vs granularity of control. Well the boss came back and said he wants ease of admin and granularity of control over security. So, does anyone have any fresh thinking on turning my eitheror into an AND.
GROUP BY Speed Up Query On Data That Is Already At Lowest Granularity?
Jul 9, 2015I have just been running a query which I was planning on improving by removing a redundant GROUP BY (there are about 20 columns, and one of the columns returned is atomic, so will mean that the "group by" will never manage to group any of the data) but when I modified the query to remove the grouping, this actually seems to slow the query, and I can't see why this would be the case.
Both queries return the same number of rows (69000), as I expected, and looking at the query plan, then they look nearly identical, other than at the start, there is a "stream aggregate" and "sort" being performed. The estimated data size is 64MB for the non-grouped query (runs in 6 min 41 secs), vs 53MB for the aggregated query (runs in 5 min 31 secs), and the estimated row size is smaller when aggregated.
Can rationalise this? In my mind, the data that is being pulled is identical, plus there is extra computation for doing an unnecessary aggregation, so the aggregated query should be unquestionably slower, but the database engine has other ideas; it seems to be able to work more quickly when it needs to do unnecessary work :) Perhaps something to do with an inefficient query plan for the non-aggregated query? I would have thought looking at the actual execution plan might have made this apparent, but both plans look very similar.
Edit: More information, the "group by" query had two aggregations on it, a count of one of the columns, and an average of another one. I changed this so that it was just "1" instead of the count, and for the average, I changed it to be the expression within the average aggregate, since the aggregation effectively does not do anything.
Analysis :: Linking Multiple Fact Table At Different Granularity To Same Dimensions
Jul 15, 2015I am modelling two fact tables of Actuals and Budget which are at different granularity, Actuals are at day, customer and product sub category level. Budgets are at month, Region and Product category level.
Month, Region and Product Category is present in Date, Region and Product Category dimension respectively. I have only three dimensions as Customer, Product and Date. Linking those dimensions to Actual Fact table is not an issue, what is the best way and options are there to link budget fact table to those three dimensions.
Difference Between Index Seek && Index Scan && Index Lookup Operations?
Oct 20, 2006please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
thank you in advance
SQL 2012 :: Full Text Index How To Make It NOT To Index Embedded Or Attached Documents
Sep 30, 2015I am using Full Text Index to index emails stored in BLOB column in a table. Index process parses stored emails, and, if there is one or more files attached to the email these documents get indexed too. In result when I'm querying the full text index for a word or phrase I am getting reference to the email containing the word of phrase if interest if the word was used in the email body OR if it was used in any document attached to the email.
How to distinguish in a Full Text query that the result came from an embedded document rather than from "main" document? Or if that's not possible how to disable indexing of embedded documents?
My goal is either to give a user an option if he or she wants to search emails (email bodies only) OR emails AND documents attached to them, or at least clearly indicate in the returned result the real source where the word or phrase has been found.
Clustered Index On Client_ID+ORderNO+OrdersubNo, If I Create 3 Noncluster Index On Said Column Will It Imporve Performance
Dec 5, 2007
Dear All.
We had Teradata 4700 SMP. We have moved data from TD to MS_SQL SERVER 2003. records are 19.65 Millions.
table is >> Order_Dtl
Columns are:-
Client_ID varchar 10
Order_ID varchar 50
Order_Sub_ID decimal
.....
...
..
.
Pk is (ClientID+OrderId+OrderSubID)
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.
The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
My Question are:-
Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?
Q3. I want to check what indexes has been used? on what search?
Q4. How can i check what table was populated when, or last date of update (DML)?
My Limitation is i Dont Create a Partioned table. I dont have permission to do it.
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
I am thank u to all who read or reply.
Arshad
Manager Database
Esoulconsultancy.com
(Teradata Master)
10g OCP
Integration Services :: Rebuild Index / Refresh Index And Stats Improves Ssis Package Performance
Oct 28, 2015My SSIS package is running very slow taking so much time to execute, One task is taking 2hr for inserting 100k records, i have disabled unused index still it is taking time.I am rebuilding/Refreshing indexes and stats once in month if i try to execute on daily basis will it improve my SSIS Package performance?Â
View 2 Replies View RelatedIndex Was Out Of Range. Must Be Non-negative And Less Than The Size Of The Collection. Parameter Name: Index
Jan 22, 2006Keep getting this error when positioning to the last page of a report.
Using Server 2003...SqlRpt Svcs 2000 sp2
Detail error msg:
Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. (rrRenderingError) Get Online Help
Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown.
Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index
Anyone have any suggestions? Any way to find out what collection is blowing?...or where parameter name: index comes from?
Index Table1 And Select For 647.600 Records.. It Is So Slow.. But I Have No Index :)???
Jun 20, 2008hello friends
i have table1 and 200 coulumn of table1 :) i have 647.600 records. i entered my records to table1 with for step to code lines in one day :)
i select category1 category2 and category3 with select code but i have just one index.. it is productnumber and it is primarykey..So my select code lines is so slow.. it is 7-9 second.. how can i select in 0.1 second ? Should i create index for category1 and category2 and category3 ? But i dont know create index.. My select code lines is below.. Could you learn me and show me index for it ?? or Could you learn me and show me fast Select code lines and index or etc ??? Also my search code line have a dangerous releated to attaching table1 with hackers :)
cheersi send 3 value of treview1 node and childnode and child.childnode to below page.aspx :)
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
If Request("TextBox1") IsNot Nothing ThenTextBox1.Text = Request("TextBox1")
End If
If Request("TextBox2") IsNot Nothing ThenTextBox2.Text = Request("TextBox2")
End If
If Request("TextBox3") IsNot Nothing ThenTextBox3.Text = Request("TextBox3")
End If
End If
Dim searchword As String
If Request("TextBox3") = "" And Request("TextBox2") = "" Then
searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "'"
End If
If Request("TextBox3") = "" Then
searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "'"
End If
If Request("TextBox3") <> "" And Request("TextBox2") <> "" And Request("TextBox1") <> "" Then
searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "' and kategori2= '" & Request("TextBox3") & "'"
End If
SqlDataSource1.SelectCommand = searchword
End Sub
The Index Entry For Row ID Was Not Found In Index ID 3, Of Table 357576312
Jul 9, 2004Hi,
I'm running a merge replication on a sql2k machine to 6 sql2k subscribers.
Since a few day's only one of the merge agents fail's with the following error:
The merge process could not retrieve generation information at the 'Subscriber'.
The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'.
All DBCC CHECKDB command's return 0 errors :confused:
I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . .
Any ideas as to what is causing this error?
Advantages Of Using Nonclustered Index After Using Clustered Index On One Table
Jul 3, 2006Hi everyone,
When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ??
In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??
Thanks
SQL 2012 :: Clustered Index Key Order In NC Index
Mar 5, 2015I have a clustered index that consists of 3 int columns in this order: DateKey, LocationKey, ItemKey (there are many other columns in this data warehouse table such as quantities, prices, etc.).
Now I want to add a non-clustered index on just one of the other columns, say LocationKey, like this:
CREATE INDEX IX_test on TableName (LocationKey)
I understand that the clustered index keys will also be added as key columns to any NC indexes. So, in this case the NC index will also get the other two columns from the clustered index added as key columns. But, in what order will they be added?
Will the resulting index keys on this new NC index effectively be:
LocationKey, DateKey, ItemKey
OR
LocationKey, ItemKey, DateKey
Do the clustering keys get added to a NC index in the same order as they are defined in the clustered index?
Clustered Index Vs. Full Text Index
Jun 18, 2008Quick question about the primary purpose of Full Text Index vs. Clustered Index.
The Full Text Index has the purpose of being accessible outside of the database so users can query the tables and columns it needs while being linked to other databases and tables within the SQL Server instance.
Is the Full Text Index similar to the global variable in programming where the scope lies outside of the tables and database itself?
I understand the clustered index is created for each table and most likely accessed within the user schema who have access to the database.
Is this correct?
I am kind of confused on why you would use full text index as opposed to clustered index.
Thank you
Goldmember
Index/performance Index For SELECT.... IN Statement
Sep 10, 2007
Hi All,
I 'm working to improve some sql performance.
One of the major syntax inside the SELECT statment is ..
WHERE FIELDA IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='A') AND
WHERE FIELDB IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='B') AND
WHERE FIELDC IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='C') AND
WHERE FIELDD IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='D') AND
WHERE FIELDE IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='E') AND
WHERE FIELDF IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='F')
(It's to compare the field content with some user input parameter inside a parameter table... )
I think properly is that the SELECT ... IN is causing much slowness in the sql statement. I have indexed FIELDA , FIELDB, FILEDC etc and those PARAVALUE and PARATYPE in the PARATABLE table. But perfromance is still slow and execution takes >20 seconds for 200000 rows of records.
Do any one know if still any chance to improvide the performance like this?
Much Thanks,
Andy
Index Internals - Last Time Index Was Rebuilt?
Apr 17, 2007I'm trying to find whether there is a dmv or system view that can help me see the last time an index was rebuilt or created. Assuming I rebuilt an index using tsql commands (not a job with a history), is there a way to find out the last time that index was rebuilt?
Thanks much.
Index Scan Vs Index Seek
Mar 1, 2004I have a really strange problem.
I execute this query:
declare @cid int
set @cid = 2003227
select * from sales s, product p where p.product_Id = s.product_Id and customer_id = @cid
select * from sales s, product p where p.product_Id = s.product_Id and customer_id = @cid or @cid = 0
3 Million rows in sales, 120000 in product.
The first does and index seek, the second an index scan.
The execution plan reports that the scan takes 99.87% of the cost, and the seek takes 0.13%
This problem obviously gets worse the bigger the dataset / query /etc.
The reason I query this, is because it never used to take this long to do index scans. Is there something i can change, something i can fix?
Any help would be appreciated.
Josh
Reorganize Index And Rebuild Index ??
Mar 18, 2008Hi,
I just want to know whether any advantage or disadvantage
in doing Reorganize Index And Rebuild Index ....
Plz do comment on this ASAP !!!!
Thanks in advance
Regards
Arv
Reorganize Index And Rebuild Index
Mar 18, 2008Hi,
I just want to know whether any advantage or disadvantage
in doing Reorganize Index And Rebuild Index ....
Plz do comment on this ASAP !!!!
Thanks in advance
Regards
Arv
Clustered Index Or NonClustered Index
Apr 1, 2006Hello I want to learn disparity clustered index or nonclustered index and in queries which one run better.
example
select * from orders where orderID=5
to this query clustered or nonclustered
thanks
Simple Query Chooses Clustered Index Scan Instead Of Clustered Index Seek
Nov 14, 2006the query:
SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WHERE a.AssociationGuid IN (
SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada
WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
takes 30-60 seconds to run on my machine, due to a clustered index scan on our an index on asset [about half a million rows]. For this particular association less than 50 rows are returned.
expanding the inner select into a list of guids the query runs instantly:
SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WHERE a.AssociationGuid IN (
'0F9C1654-9FAC-45FC-9997-5EBDAD21A4B4',
'52C616C0-C4C5-45F4-B691-7FA83462CA34',
'C95A6669-D6D1-460A-BC2F-C0F6756A234D')
It runs instantly because of doing a clustered index seek [on the same index as the previous query] instead of a scan. The index in question IX_Asset_AssociationGuid is a nonclustered index on Asset.AssociationGuid.
The tables involved:
Asset, represents an asset. Primary key is AssetGuid, there is an index/FK on Asset.AssociationGuid. The asset table has 28 columns or so...
Association, kind of like a place, associations exist in a tree where one association can contain any number of child associations. Each association has a ParentAssociationGuid pointing to its parent. Only leaf associations contain assets.
AssociationDataAssociation, a table consisting of two columns, AssociationGuid, DataAssociationGuid. This is a table used to quickly find leaf associations [DataAssociationGuid] beneath a particular association [AssociationGuid]. In the above case the inner select () returns 3 rows.
I'd include .sqlplan files or screenshots, but I don't see a way to attach them.
I understand I can specify to use the index manually [and this also runs instantly], but for such a simple query it is peculiar it is necesscary. This is the query with the index specified manually:
SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WITH (INDEX (IX_Asset_AssociationGuid)) WHERE
a.AssociationGuid IN (
SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada
WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
To repeat/clarify my question, why might this not be doing a clustered index seek with the first query?
Change Nc-index To C-index?
Jun 29, 2004Found out a while back that my facts-tabel has an non-clustered index on its facts_id. In a bunch of procedures an update is executed against a facts_id unfortunately on it's facts-table. I was wondering if changing it into a clustered index is worth the effort / would make sense considering a +110 million facts and re-indexing the other indexes as well? Facts are loaded sequentially, so I would suspect them facts are in the ordered already?
thanx,
Index Seek, Index Scan, Table Scan
Oct 4, 2007
Hey,
what is the difference between Table Scan und Index Scan?
I find no difitions in the internet
Finchen
What Is Table Scan, Index Scan And Index Seek??
Sep 21, 2007Hi,
I want to know wht is a
TABLE SCAN
INDEX SCAN
INDEX SEEKand When they are used, Wht is the difference between all these.????
Include Clustered Index In Non-clustered Index?
Oct 15, 2007Hi everybody!
I just ran the Database Engine Tuning Advisor on a relative complex query to find out if a new index might help, and in fact it found a combination that should give a performance gain of 94%. Fair enough to try that.
What I wonder about: The index I should create contains 4 columns, the last of them being the Primary Key column of the table, which is also my clustered index for the table. It is an identity integer btw.
I think I remember that ANY index does include the clustered one as lookup into the data, so having it listed to the list of columns will not help. It might at worst add another duplicate 4 bytes to each index entry.
Right? Wrong? Keep the column in the index, or remove it since it is included implicit anyway?
Thanks for suggestions!
Ralf
Index Help
Jun 27, 2001Hello List,
I've couple of tables in my database. I created some clustered indexes and non-clustered indexes on these tables. But surprisingly, When I run sp_helpindex table_name, I see a lot of indexes starting from WA_ name. I dont know, what are these indexes and I believe, I havent created them.
Would like to know, how does these got created and is it appropriate to delete them?
Please advice
Thanks in advance
Sindi
Need Help With Index
Aug 1, 2001I have table pcCode
sp_help pcCode
Column_name
-----------
ID_Code
CodeType
ID_Property
ID_Reference
Name
ShortName
ViewOrder
ParentType
Status
index_description index_keys
------------------------------------------------------------------------------clustered, unique, primary key located on PRIMARY ID_Code
nonclustered, unique key located on PRIMARY ID_Property, CodeType, ShortName
Now
if I do
select Status from prcode where ID_Property = 7
Will SQl server use index created on 3 columns (becouse ID_Property first in the index )
or I need another index on ID_Property column only
Thanks
Index
Sep 7, 2001This index "_WA_Sys_title_318258D2" was auto created by the system (nonclustered, statistics, auto create located on Primary). If I want
it remove will I have to drop the table because the command Drop index...
doesn't remove it, the message "because it does not exist in the system catalog." will come up and were can I turn of the command so it won't be create by the system ?