Durability: After a transaction has completed, its effects are permanently
Processes (such as Analysis Services) running in Windows 2000 Server or Windows Server 2003 Standard Edition can address a maximum of 2 gigabytes (GB) of RAM in the main process space. If you are working with large or complex cubes, Analysis Services may require more than 2 GB to load dimensions into memory, process dimensions, load replica dimensions, and still have sufficient memory for an effective query results cache. To allow Analysis Services to address more than 2 GB of RAM in a single process, you must install Windows 2000 Advanced Server; Windows 2000 Datacenter; Windows Server 2003 Enterprise Edition; or Windows Server 2003 Datacenter Edition. Windows Server 2003 Enterprise Edition and Windows Server 2003 Datacenter Edition, are available in 32-bit and 64-bit versions. The 64-bit version supports the 64-bit version of Analysis Services. Because Windows 2000 Advanced Server and Windows 2000 Datacenter Server are 32-bit operating systems, only the 32-bit version of Analysis Services can be installed. The 64-bit version of Analysis Services can address all available memory in the main process space without any special configuration (up to 64 GB with the Enterprise Edition and up to 512 GB with the Datacenter Edition). The 32-bit version of Analysis Services can address up to 3 GB of memory in the main process space, if you enable Application Memory Tuning. Unless you enable Application Memory Tuning, no process can address more than 2 GB in the main process space. To enable Application Memory Tuning on the Analysis Services computer, set the /3 GB switch in the boot.ini file and then use Analysis Manager to set an appropriate Memory conservation threshold value for Analysis Services. If you set the /3GB switch in boot.ini, the computer on which Analysis Services is running should have at least 4 GB of memory to ensure that the Windows operating system has sufficient memory for system services. If you are running other applications on the same computer, you must factor in their memory requirements as well. For example, if the SQL Server service and Analysis Services are installed on the same computer, SQL Server can address memory above 4 GB because SQL Server supports Address Windowing Extensions (AWE). In this case, you could install and use 8 GB or more on the server. However, because Analysis Services does not support AWE, Analysis Services cannot access more the 3 GB of memory in the main process space unless the 64-bit version is used. Having sufficient memory for Analysis Services increases query responsiveness and processing performance. Properly configuring available memory will maximize the use of memory, limit the use of disk resources for processing, and prevent the cleaner thread from evicting cache entries too quickly. The amount of memory used by Analysis Services for various purposes is regulated by a number of memory settings:
- High and low memory settings
- Very Large Dimension Memory (VLDM) threshold setting
- Process buffer settings
These settings are configured using default values or based on the amount of physical memory in the computer during installation. Changing some of these memory settings is generally recommended.
High and Low Memory Settings
Analysis Services employs a number of mechanisms to keep the amount of memory allocated to it within the range that is defined by two settings on the Environment tab of the Server Properties dialog box in Analysis Manager: the Memory conservation threshold and the Minimum allocated memory settings (the HighMemoryLimit and the LowMemoryLimit values in the registry). The default value for the Memory conservation threshold setting is the amount of physical memory on the computer at the time of installation. The default value for the Minimum allocated memory setting is half the amount of physical memory on the computer at the time of installation. If you change the amount of memory on the computer after installation, you must manually modify these values. Otherwise, Analysis Services will not properly utilize the actual amount of physical memory on the computer.
When the amount of memory used by Analysis Services exceeds the Memory conservation threshold setting, Analysis Services increases the priority of the cleaner thread to normal in order to quickly reduce the allocated memory to the Minimum allocated memory setting. If the total memory allocated to all Analysis Services tasks exceeds the memory conservation threshold by more than approximately 6.25 percent, Analysis Services immediately begins dropping the cache entries for entire cubes in order to quickly reduce the amount memory used by Analysis Services. In this scenario, because Analysis Services is shedding memory extremely quickly, the total amount of allocated memory may drop below the Minimum allocated memory setting. If you set the Minimum allocated memory setting too low, the cleaner thread removes too many cached entries from the query results cache.
Joins are a mandatory concept for T-SQL skill. I have prepared following refresher for preliminary learners of T-SQL. Create Tables to use in refresher
USE pubs GO CREATE TABLE t1(f1 INT) CREATE TABLE t2(f1 INT) CREATE TABLE t3(f1 INT) GO
now populate the tables
--=========for t1================== SET NOCOUNT ON DECLARE @j INT SET @j = 1 WHILE @j <= 10 BEGIN INSERT INTO t1 VALUES(@j) SET @j = @j + 1 END GO --=========for t2================== SET NOCOUNT ON DECLARE @k INT SET @k = 6 WHILE @k <= 15 BEGIN INSERT INTO t2 VALUES(@k) SET @k = @k + 1 END GO --==========for t3================= INSERT INTO t3 VALUES(6) INSERT INTO t3 VALUES(2) INSERT INTO t3 VALUES(3) INSERT INTO t3 VALUES(12) INSERT INTO t3 VALUES(13) GO
Now t1, t2, t3 has following values
t1 t2 t3
1 6 6
2 7 7
3 8 8
4 9 9
5 10 10
6 11 11
7 12 12
8 13 13
- Inner Join Query
Inner joins return rows only when there is at least one row from both tables, that matches the join condition.
Inner joins eliminate the rows that do not match with a row from the other table
SELECT * FROM T1 JOIN t2 ON t1.f1 = t2.f1 GO
SELECT * FROM t1 INNER JOIN t2 ON t1.f1 = t2.f1 GO
SELECT * FROM t1,t2 WHERE t1.f1 = t2.f1 GO ================================
- Outer Join Query
Outer joins, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions.
All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join.
All rows from both tables are returned in a full outer join.
Both queries below will have same result, as both use left outer join
SELECT t1.f1, t2.f1 FROM t1 LEFT OUTER JOIN t2 ON (t1.f1 = t2.f1) GO --========================== SELECT t1.f1, t2.f1 FROM t1 LEFT JOIN t2 ON (t1.f1 = t2.f1) GO
Right outer join is used to get all values from right side table in query, ignoring the fact that values match in table on left side or not.
Both queries below will have same result, as both use right outer join
SELECT t1.f1, t2.f1 FROM t1 RIGHT OUTER JOIN t2 ON (t1.f1 = t2.f1) GO --============================== SELECT t1.f1, t2.f1 FROM t1 RIGHT JOIN t2 ON (t1.f1 = t2.f1) GO
Full outer join is used to get values from both the table, either values are matched in both tables or not.
Both queries below will have same result, as both use full outer join
SELECT t1.f1, t2.f1 FROM t1 FULL OUTER JOIN t2 ON (t1.f1 = t2.f1) ORDER BY t1.f1 GO --====================== SELECT t1.f1, t2.f1 FROM t1 FULL JOIN t2 ON (t1.f1 = t2.f1) ORDER BY t1.f1 GO
- Multiple Joins
JOINS make much sense between two tables, but can obviously be extended to more than two tables.
Full outer join in three tables will get values from all three tables
SELECT a.f1, b.f1, c.f1 FROM t1 a FULL OUTER JOIN t2 b ON (a.f1 = b.f1) FULL OUTER JOIN t3 c ON (a.f1 = c.f1) GO
- Self Joins
When the same table is used in a query with two different aliases,
such a join is referred to as a self-join.
- Cross Joins
A cross join is referred to as a Cartesian product,which means, for each element in set-A pick all the values from set-B